SELECT * FROM RESULTADOS WHERE AMOSTRA = '2010102827003106'



create table funcionario 
(
	cod_func int not null primary key,
	nome_func varchar(50) not null,
	sexo_func char(1),
	sal_func decimal(10,2) not null

)type = InnoDB;



create table dependente 
(
	cod_dep int not null primary key,
	cod_func int not null references funcionario(cod_func),
	nome_dep varchar(50) not null,
	sexo_func char(1)
)type = InnoDB;




create table dependente 
(
	cod_dep int not null primary key,
	cod_func int not null references funcionario(cod_func),
	nome_dep varchar(50) not null,
	sexo_func char(1)
)type = MyISAM;



drop table dependente;

create table dependente 
(
	cod_dep int not null primary key ,
	cod_func int not null references funcionario(cod_func),
	nome_dep varchar(50) not null,
	sexo_func char(1) 
);


insert into funcionario values(2,'Zeh Manuel','m',600.50);


insert into dependente values(3,1,'Brianna Banks','f');


select * from dependente;

insert into dependente values(4,3,'Brianna Banks','f');

select * from dependente;





create table dependente 
(
	cod_dep int not null primary key,
	cod_func int not null,
	nome_dep varchar(50) not null,
	sexo_func char(1),
	foreign key (cod_func) references funionario(cod_func) on delete cascade
);


insert into dependente values(7,8,'Brianna Banks','f');


drop table dependente;


create table dependente 
(
	cod_dep int not null primary key,
	cod_func int not null,
	nome_dep varchar(50) not null,
	sexo_func char(1),
	foreign key (cod_func) references funionario(cod_func)
)type = InnoDB;



CREATE TABLE `onix`.`dependente` (
  `cod_dep` INT  NOT NULL AUTO_INCREMENT primary key,
  `cod_func` INT  NOT NULL,
  CONSTRAINT `new_fk_constraint` FOREIGN KEY `new_fk_constraint` (cod_func)
    REFERENCES `funcionario` (cod_func)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;

insert into dependente values(4,5);

create table filho(
	cod_filho int not null primary key auto_increment,
	cod_func int not null,
	nome_filho varchar(50)not null,
	constraint fk_filho foreign key (cod_func) references funcionario (cod_func) on delete restrict on update restrict
)type=InnoDB;


insert into filho value(1,1,"Zeh");


create table aluno(
	cod_aluno int not null primary key auto_increment,
	nome_aluno varchar(50) not null,
	sexo_aluno ENUM('F','M') not null

)type = MyISAM;


create table curso(
	cod_curso int not null primary key auto_increment,
	nome_curso varchar(100) not null unique
)type = MyISAM;



insert into aluno values(4,"Ana Joana",'F')
insert into aluno values(5,"Maria Ana",'F')
insert into aluno values(6,"Roberto Picareta",'M')

insert into aluno values(4,"Eriko Pilantra",'M')

insert into curso values(4,"Geografia");

CREATE TABLE `test`.`PERSON_has_ADRESS` (
  `FKPERSONpersonID` INT UNSIGNED UNSIGNED NOT NULL,     
  `FKADRESSadressID` INT UNSIGNED UNSIGNED NOT NULL,    
  CONSTRAINT `FKPERSON` FOREIGN KEY `FKPERSON` (`FKPERSONpersonID`)
    REFERENCES `test`.`PERSON` (`personID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FKADRESS` FOREIGN KEY `FKADRESS` (`FKADRESSadressID`)
    REFERENCES `test`.`ADRESS` (`adressID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB;


Show innoDB status;
constraint fk_filho foreign key (cod_func) references funcionario (cod_func) on delete restrict on update restrict


create table cursoaluno(
	cod_curso int not null primary key,
	cod_aluno int not null ,
	constraint fk_aluno foreign key (cod_aluno) references aluno (cod_aluno) on delete restrict on update restrict

)type=InnoDB;

create table curso_aluno(
	cod_ca int not null primary key auto_increment,
	cod_ca_aluno int not null,
	constraint fk_aluno foreign key (cod_ca_aluno) references aluno (cod_aluno) on delete restrict on update restrict
)type=InnoDB;

drop table curso_aluno;


create table aluno_curso(
	cod_aluno int not null,
	cod_curso int not null,
	constraint primary key(cod_aluno,cod_curso),
	constraint fk_aluno foreign key (cod_aluno) references aluno (cod_aluno) ON DELETE NO ACTION ON UPDATE NO ACTION,
	constraint fk_curso foreign key (cod_curso) references curso (cod_curso) ON DELETE NO ACTION ON UPDATE NO ACTION
)type=InnoDB;



insert into aluno_curso values(3,3);




create table cliente8 (
	cod_cli int not null,
	nome_cli varchar(50) not null,
	sexo_cli enum('F','M') not null default 'M',
	sal_cli decimal (10,2) not null,
	constraint PK_cli8 primary key(cod_cli)
)type = InnoDB;
select * from cliente8;

insert into cliente8 values(1,"Ana de Souza",'F',1500.23);
insert into cliente8 values(2,"Mariana de Souza",'F',2800.15);
insert into cliente8 values(3,"Fabiana de Souza",'F',287.56);
insert into cliente8 values(4,"Rosana de Souza",'F',5589.58);
insert into cliente8 values(5,"Adriana de Souza",'F',6785.59);
insert into cliente8 values(,"",'',);


create table funcionario8(
	cod_func int not null,
	nome_func varchar(50) not null,
	sal_func decimal(10,2) not null check(sal_func > 0) ,
	constraint PK_func8 primary key(cod_func) 
)type=InnoDB;

insert into funcionario8 values(1,"Anatalino Reguete",4500.00);
insert into funcionario8 values(2,"Romeu Pompilho",4500.00);
insert into funcionario8 values(3,"Antonieta Antonino",3500.00);
insert into funcionario8 values(4,"Amelia Amalia",3500.00);
insert into funcionario8 values(5,"Crisantemo Cristalino",6000.00);
insert into funcionario8 values(6,"Amim Amou Amado",10000.00);

select * from funcionario8;


create table aluno8(
	cod_aluno int not null,
	nome_aluno varchar(50) not null,
	sexo_aluno enum('F','M') not null default'M',
	constraint PK_aluno8 primary key(cod_aluno) 
)type=InnoDB;


insert into aluno8 values(1,"Pompeu de Tibirica",'M');
insert into aluno8 values(2,"Amaro Amarildo",'M');
insert into aluno8 values(3,"Marcia Mirtes",'F');

select * from aluno8;

select 
cod_cli as cod_pes,
'Cliente' as tipo_pes,
nome_cli as Nome_Pes,
sexo_cli as Sexo_Pes,
sal_cli as Sal_Pes
from cliente8

union all

select 
cod_func,
'Funcionario',
nome_func,
'Em Branco',
sal_func
from funcionario8

union all

select 
cod_aluno,
'Aluno',
nome_aluno,
sexo_aluno,
null
from aluno8
order by 2;





create table cargo8(
	cod_cargo int not null,
	nome_cargo varchar(50) not null unique ,
	constraint PK_cargo8 primary key(cod_cargo)
)type = InnoDB;

insert into cargo8 values(1,"Diretor Presidente");
insert into cargo8 values(2,"Diretor de Departamento");
insert into cargo8 values(3,"Supervisor");
insert into cargo8 values(4,"Analista de Sistemas");
insert into cargo8 values(5,"Analista de Negocios");

create table funcionario8_2(
	cod_func int not null,
	cod_cargo int not null,
	nome_func varchar(50) not null,
	sal_func decimal(10,2)not null check(sal_func > 0),
	rg_func char(15) not null unique,
	constraint 	PK_cod_func8 primary key(cod_func)
)type = InnoDB;

insert into funcionario8_2 values(1,5,"Paulinha Reguete",4500.00,"15.253.587");
insert into funcionario8_2 values(2,5,"Romeu Pompilio",4500.00,"14.258.457");
insert into funcionario8_2 values(3,3,"Antonieta Antonio",3500.00,"17.258.368");
insert into funcionario8_2 values(4,3,"Amelia Amalia",5500.00,"18.963.458");
insert into funcionario8_2 values(5,2,"Crisantemo Cristiano",6000.00,"16.258.147");
insert into funcionario8_2 values(6,1,"Amim Amou Amado",10000.00,"14.235.258");

create table dependente8_2(
	cod_dep int not null,
	cod_func int not null,
	nome_dep varchar(50) not null,
	constraint PK_dep8_2 primary key (cod_dep),
	constraint FK_dep8_2 foreign key (cod_func) references funcionario8_2(cod_func)
)type InnoDB;

insert into dependente8_2 values(1,1,"Rosalina Reguete");
insert into dependente8_2 values(2,1,"Marcelina Reguete");
insert into dependente8_2 values(3,1,"Claudomiro Reguete");
insert into dependente8_2 values(4,2,"Amarildo Pomposo");
insert into dependente8_2 values(5,2,"Anacleto Pomposo");
insert into dependente8_2 values(6,5,"Pepino Crisantemo");
insert into dependente8_2 values(7,5,"Natalino Crisantemo");


select * from cargo8
where cod_cargo not in(select cod_cargo from funcionario8_2);


select * from cargo8
where cod_cargo in(select cod_cargo from funcionario8_2) 
order by 1;


select * from cargo8
where cod_cargo
in (select cod_cargo from funcionario8_2
   where cod_func in(select cod_func from dependente8_2)) order by 1 ;


select * from cargo8
where cod_cargo
in (select cod_cargo from funcionario8_2
   where cod_func not in(select cod_func from dependente8_2)) order by 1 ;


select * from funcionario8_2;

select * from funcionario8_2
where sal_func = (select max(sal_func) from funcionario8_2);


select max(sal_func) from funcionario8_2;

select * from funcionario8_2
where sal_func = (select min(sal_func) from funcionario8_2);


select * from funcionario8_2
where sal_func = (select min(sal_func) from funcionario8_2 where
cod_func in(select cod_func from dependente8_2));


select *,(select count(*)
 from dependente8_2 
 where cod_func = cond_func) as qtd_dep
from funcionario8_2;

select count(*) as qtd_dep from dependente8_2
where cod_func = 1 ;

select *,(select count(*) from dependente8_2
where cod_func = funcionario8_2.cod_func) as qtd_dep
from funcionario8_2;


select cod_cargo,nome_cargo,(select count(*) from funcionario8_2
where cod_cargo = cargo8.cod_cargo) as qtd_func
from cargo8
order by 1;


update funcionario8_2
set sal_func = sal_func * 1.1
where cod_func not in (select cod_func from dependente8_2);

create table funcionario_safado8_2(
	cod_saf int not null,
	cod_func int not null,
	constraint PK_safado primary key(cod_saf),
	constraint FK_safado foreign key(cod_func) references funcionario8_2(cod_func) 
)type = InnoDB;

select * from funcionario8_2;
insert into funcionario8_2 values(7,5,"Tadeu Pilantra",5000.00,"766.23.434");

insert into funcionario_safado8_2 values(1,7);

delete from funcionario8_2
where cod_func  in (select cod_func from funcionario_safado8_2);
/*
not working cause child table references to funcinario8_2
*/

update funcionario8_2
set sal_func = sal_func + 10000.00
where cod_func in (select cod_func from funcionario_safado8_2);

select * from funcionario8_2;

create table produto9(
	 cod_prod int not null,
	tipo_prod char(1) not null,
	linha_prod tinyint not null,
	nome_prod varchar(50) not null,
	qtd_prod int not null,
	val_prod decimal(10,2) not null
)type=InnoDB;


insert into produto9 values(1,'A',1,"Mesa",100,500.00);
insert into produto9 values(2,'B',1,"Sofah",200,1500.00);
insert into produto9 values(3,'A',2,"Mesa",100,200.00);
insert into produto9 values(4,'A',2,"Armario",300,200.00);
insert into produto9 values(5,'C',1,"Cama",500,150.00);
insert into produto9 values(6,'B',2,"Poltrona",100,250.00);
insert into produto9 values(7,'B',2,"Sofa",200,300.00);
insert into produto9 values(8,'C',2,"Cama",100,800.00);
insert into produto9 values(9,'A',1,"Armario",200,800.00);
insert into produto9 values(10,'C',1,"Guarda-Roupa",100,1500.00);







select tipo_prod,sum(qtd_prod) as qtd_estoque
from produto9
group by tipo_prod;


select linha_prod,sum(qtd_prod) as 'quantidade em estoque'
from produto9
group by linha_prod;


select * from produto9 order by 1;

update produto9
set val_prod = 150.00
where cod_prod = 8;
update produto9
set linha_prod = 1
where cod_prod = 6;



select tipo_prod,linha_prod,sum(qtd_prod) as "Quantidade em Estoque"
from produto9
group by tipo_prod;


select tipo_prod,linha_prod,sum(qtd_prod) as "Quantidade em Estoque"
from produto9
group by tipo_prod,linha_prod
order by 1;

select tipo_prod,sum(qtd_prod*val_prod)as 'Valor total em estoque por produto'
from produto9
group by tipo_prod;


select nome_prod as 'Nome do Produto',
sum(qtd_prod) as 'Quantidade total em estoque',
sum(qtd_prod*val_prod) as 'Valor total em estoque por produto'
from produto9
group by nome_prod;


select * from produto9;

select tipo_prod,
sum(qtd_prod) as 'Quantidade total em estoque por produto'
from 
produto9
group by tipo_prod
having sum(qtd_prod)>500 ;


select tipo_prod,
sum(qtd_prod) as 'Quantidade total em estoque por produto'
from 
produto9
group by tipo_prod
having sum(qtd_prod)>300 ;


select tipo_prod,
linha_prod,
sum(qtd_prod) as 'Quantidade total em estoque por produto'
from 
produto9
group by tipo_prod,linha_prod
having sum(qtd_prod)>300 ;

select nome_prod as 'Nome do Produto',
sum(qtd_prod) as 'Quantidade em Estoque',
sum(qtd_prod*val_prod) as 'Valor total em Estoque'
from produto9
group by nome_prod
having sum(qtd_prod) >= 500
and
sum(qtd_prod*val_prod) > 220000.00;


select * from funcionario8_2;


show tables;


select * from MESSAGE;

select * from Chap2;

private Long id;
	private String chapText;
	private String chapObs;

create table Chap2(
	id bigint not null auto_increment ,
	chapText varchar(255) not null,
	chapObs varchar(255) not null,
	constraint PK_chap2 primary key (id)
)type = InnoDB;


	create table Chap3(
		CHAP3_ID bigint not null auto_increment,
		CHAP3_TEXT varchar(255),
		CHAP3_OBS varchar(255),
		CHAP3_CHAP3_ID bigint,
		constraint PK_CHAP3 primary key (CHAP3_ID),
		constraint FK_CHAP3 foreign key(CHAP3_CHAP3_ID) references Chap3(CHAP3_ID)
	)type = InnoDB; 
	show tables;
	
	select * from Chap3;

drop table MESSAGE;

show tables; select * from MESSAGE;

select * from ENTITY_CLASS;


	drop table DADY;
	
	
	create table DADY(
		DADY_ID bigint not null auto_increment,
		dadName varchar(255) not null,
		dadAge int,
		constraint PK_dady primary key (DADY_ID)
	
	)type = InnoDB;
	
	drop table User;
	select * from User;
	select * from Filho;
	select * from Pai;
	
	show tables;
	
	insert into Pai values(1,"Pai Class");
	insert into Filho values(9,"Filho Class",1);
	
	create table Person(
		person_id bigint not null auto_increment,
		person_name varchar(255) not null,
		constraint PK_person primary key (person_id) 
	)type = InnoDB;
	
	create table Event(
		event_id bigint not null auto_increment,
		event_desc varchar(255) not null,
		constraint PK_event primary key(event_id)
	)type = InnoDB;

create table PersonEvent(
person_id bigint not null,
event_id bigint not null,
constraint FK_person foreign key(person_id) references Person (person_id),
constraint FK_event foreign key(event_id) references Event (event_id)
)type = InnoDB;

insert into Person values(1,"Eros Koller");
insert into Event values(1,"Estudar Programacao");
insert into Event values(2,"Estudar Muito Mais");
insert into PersonEvent values(1,1);

select * from Person;
select * from Event;
select * from PersonEvent;


show tables;

create table Pessoa(
	id_Pessoa bigint not null auto_increment,
	age int,
	firstName varchar(255),
	lastName varchar(255),
	constraint PK_Pessoa primary key(id_Pessoa)
)type = InnoDB;






create table Evento(
	id_Evento bigint not null auto_increment,
	title varchar(255) not null,
	dataEvento date not null,
	constraint PK_Evento primary key (id_Evento)
)type = InnoDB;
	


select * from Evento;

show tables;

create table Pessoa_Evento(
	id_Pessoa bigint not null,
	id_Evento bigint not null,
	constraint FK_Pessoa foreign key(id_Pessoa) references Pessoa(id_Pessoa),
	constraint FK_Evento foreign key(id_Evento) references Evento(id_Evento)
)type = InnoDB;

drop table Evento;
select * from Pessoa_Evento;
insert into Pessoa_Evento values(10,1);




create table Krap(
	id_Krap bigint not null auto_increment,
	krap_Text varchar(255) not null,
	constraint PK_Krap primary key (id_Krap)
)type = InnoDB;

create table Dust(
	id_Dust bigint not null auto_increment,
	dust_Text varchar(255) not null,
	constraint PK_Dust primary key (id_Dust)
)type = InnoDB;

drop table Dust;
select * from Krap;



create table Dust_Krap(
	id_Dust bigint not null,
	id_Krap bigint not null,
	constraint FK_Dust foreign key(id_Dust) references Dust(id_Dust),
	constraint FK_Krap foreign key(id_Krap) references Krap(id_Krap)
)type = InnoDB;

select * from Dust_Krap;

select * from Pessoa;

create table Email(
	id_Pessoa bigint not null,
	email varchar(255) not null ,
	constraint PK_email primary key(id_Pessoa,email),
	constraint FK_email foreign key(id_Pessoa) references Pessoa(id_Pessoa)

)type = InnoDB;

drop table Email;
select * from Email;




create table Produt(
	codProd bigint not null auto_increment,
	codProdFactory varchar(50) not null unique,
	nameProd varchar(50) not null,
	typeProd varchar(15) not null,
	typeProdDetail varchar(25) not null,
	prodClass varchar(10),
	descProd varchar(255) ,
	priceToBuy double(10,2),
	priceToSell double(10,2),
	maxStoke bigint,
	minStoke bigint,
	reseved bigint,
	stroke bigint,
	running boolean,
	constraint PK_PROD primary key(codProd)

)type = InnoDB;



create table Costumer(
	codCost bigint not null auto_increment,
	nickName varchar(20) not null unique,
	firstNameCost varchar(15) not null,
	lastNameCost varchar(50) not null,
	email varchar(40) not null,
	cep varchar(10) not null,
	brithDate date ,
	address varchar(100),
	addressNumber char(7) not null,
	addressComplement varchar(30) default "" ,
	bairroCost varchar(30) ,
	stateCost varchar(30),
	cityCost varchar(30),
	macCost varchar(100),
	ipCost varchar(100),
	constraint PK_COSTOMER primary key(codCost)
)type = InnoDB;

insert into Costumer values (1,
"usertest",
"Teste",
"Cliente",
"tartugo@gmail.com",
"03543080",
null,
"Euclid Ave",
"200a",
null,
"DownTwon",
"Ontario",
"Toronto",
"97978737687293786243677837989833g4bfhcgjh2387ds87928ghj",
"192.168.0.101"
);


drop table Produts;

select * from Produts;

insert into Produt values(4,"hhbb104",
"Estimulador de nao sei oque",
"Acessorio","acessorio p acessar",
"sei lah",
"super estimulante powerhyper",
null,
100.00,
150.00,
1000,
100,
500,
false);



select * from Costumer;

update Costumer
set Costumer.bairroCost = "Downtown"
where Costumer.codCost = 1;
;
create table book(
	id int(11) not null default '0',
	bockname varchar(50) default 'null',
	constraint PK_primer primary key (id)
)engine=MyISAM;






create table Member(
loginName varchar(20) not null,
createDate date ,
password char(255) not null,
lastName varchar(50),
firstName varchar(40),
street varchar(50),
city varchar(50),
state char(2),
zip char(10),
email varchar(50),
phone char(15),
fax char(15),
primary key (loginName)
);

alter
table
Member
MODIFY
lastName
varchar(50);

insert into Member values
("eros",
NULL,
"namnambulu",
"koller",
"eros",
"Rua Sao Donato",
"Sao Paulo",
"SP",
"03543080",
"eroskoller@gmail.com",
"09987876",
"0098776"
);

select firstName,lastName from Member
where lastName = "koller"o
and password = "namnambulu"
and (phone like "%8%" or fax like "%8%")
and state = "SP"


update Member
set phone = "26852192"
where loginName = "eros"

delete from Member
where
loginName = "eros"

/*  <<<<<<<<<<<<<<<<<<<<<<<<Oracle>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
select * from ERP_ITEM
where ITM_ST_CODIGO = 0004178


SELECT
 usu_st_codigo,
 usu_st_nome,
 usu_st_senha,
 pus_st_codigo,
 usu_st_email,
 erp_st_usuario,
 usu_st_portal,
 usu_in_diasrelatorio,
 usu_ch_ativo,
 TO_CHAR(sysdate + .125, 'hh24:mi:ss') AS uon_hr_expira
FROM lab_usuario
 WHERE usu_st_codigo = 'eros'
 AND
 usu_st_senha = 'daftpunk';


SELECT *
  FROM ERP_PEDIDO
 WHERE PED_ST_STATUS = '4'
   AND USU_ST_CODIGO = 'ZORAK'
   AND (TO_DATE(SYSDATE, 'dd/mm/yyyy') >= TO_DATE(PED_DT_ENTREGAPREV, 'dd/mm/yyyy')
   OR TO_DATE(SYSDATE, 'dd/mm/yyyy') >= TO_DATE(PED_DT_ENTREGUE, 'dd/mm/yyyy')
   )













SELECT UCC.ccu_st_codigo FROM
    erp_usuarioccu UCC
	WHERE UCC.usu_st_codigo = '7C'


select UCC.usu_st_codigo from
erp_usuarioccu UCC


SELECT ORI.ori_st_descricao FROM lab_origem ORI,erp_ccusto CCU WHERE ORI.uni_st_codigo = CCU.uni_st_codigo
	AND ORI.ori_st_codigo = SUBSTR(CCU.ccu_st_codigo, 13, 6)




SELECT
    CCU.ccu_st_codigo, CCU.ccu_st_descricao, CCU.est_st_codigo, CCU.est_st_descricao,
    RGI.rgi_st_codigo, RGI.rgi_st_descricao, RGI.rgi_st_uf, CCU.uni_st_codigo,
    CCU.ccu_ch_pai, CCU.ccu_ch_obrigatorioposto,

	(SELECT UNI.uni_st_descricao FROM lab_unidade UNI WHERE UNI.uni_st_codigo = CCU.uni_st_codigo)
    AS uni_st_descricao,

    (SELECT ORI.ori_st_descricao FROM lab_origem ORI WHERE ORI.uni_st_codigo = CCU.uni_st_codigo
	AND ORI.ori_st_codigo = SUBSTR(CCU.ccu_st_codigo, 13, 6)) AS ori_st_descricao,

	CCU.ccu_st_ativo, UCC.ucc_ch_pedido FROM erp_ccusto CCU, erp_regional RGI, erp_usuarioccu UCC
	WHERE CCU.ccu_st_codigo = UCC.ccu_st_codigo AND RGI.rgi_st_letra = SUBSTR(CCU.est_st_codigo, 0, 1)
	AND CCU.ccu_st_codigo IN (SELECT UCC.ccu_st_codigo FROM
    erp_usuarioccu UCC
	WHERE UCC.usu_st_codigo = '7C')
    AND UCC.usu_st_codigo = '7C'
	AND CCU.ccu_st_ativo = 1







SELECT RGI.rgi_st_codigo
FROM erp_regional RGI
WHERE RGI.rgi_st_codigo = 'c7'










show tables;


select * from A_User;

select userLoginName from UserSys
where userLoginName like 'Eros';





create table PACIENTES(

);



show tables;





delete from Cidade
where id = 1;





SELECT SEQ_PAC_IN_CODIGO.NEXTVAL AS PROXIMO FROM DUAL;

select
        SEQ_PAC_IN_CODIGO.nextval
    from
        dual;

select
        SEQ_REQ_ST_CODIGO.nextval
    from
        dual;



SELECT DET.EXA_ST_CODIGO, EXA.EXA_ST_DESCRICAO, DET.LEG_ST_CODIGO
FROM LAB_REQUISICAO REQ, LAB_DETALHEREQUISICAO DET, LAB_EXAME EXA
WHERE REQ.REQ_ST_CODIGO = DET.REQ_ST_CODIGO
AND DET.EXA_ST_CODIGO = EXA.EXA_ST_CODIGO
AND REQ.PAC_IN_CODIGO = 0921279321



SELECT REQ.UNI_ST_CODIGO,REQ.PAC_IN_CODIGO FROM LAB_REQUISICAO REQ, LAB_PACIENTE PAC
WHERE REQ.PAC_IN_CODIGO = PAC.PAC_IN_CODIGO
AND REQ.PAC_IN_CODIGO = 13594274



select CON.CON_ST_CODIGO, CON.CON_ST_DESCRICAO, CON.CON_ST_CNPJ
from LAB_CONVENIO CON, LAB_CONVENIOUNIDADE CU
where con.con_st_codigo = CU.con_st_codigo
AND CU.cun_ch_ativo = 'S'
AND CON.CON_CH_ATIVO = 'S'
AND CU.UNI_ST_CODIGO = 'FAR'


select CON.CON_ST_CODIGO, CON.CON_ST_DESCRICAO, CON.CON_ST_CNPJ
from LAB_CONVENIO CON, LAB_CONVENIOUNIDADE CU
where con.con_st_codigo = CU.con_st_codigo

AND CU.UNI_ST_CODIGO = 'FAR'





SELECT ORI.ORI_ST_CODIGO,
ORI.ORI_ST_DESCRICAO,
ORI.ORI_ST_ESTADO,
ORI.LEG_ST_CODIGOINICIAL,
ORI.ORI_CH_URGENCIA,
ORI.ORI_CH_IMPRIMECOLETA,
ORI.ORI_CH_IMPRIMEETIQUETA,
ori.ori_ch_confereautomatico,
ori.ori_ch_confereautomaticoFat,
ORI_CH_ETIQUETACOMPROVA FROM LAB_ORIGEM ORI WHERE ORI.UNI_ST_CODIGO = 'MAN'


SELECT LOC_ST_CODIGO, LOC_ST_DESCRICAO FROM LAB_LOCAL WHERE UNI_ST_CODIGO = 'MAN'




SELECT UNI_ST_CODIGO,ORI_ST_CODIGO,CON_ST_CODIGO,REG_ST_CODIGO FROM LAB_REQUISICAO WHERE REQ_ST_CODIGO = '0924531582'


SELECT CON_ST_CODIGO,REG_ST_CODIGO FROM LAB_ORIGEM WHERE UNI_ST_CODIGO = 'BRU' AND ORI_ST_CODIGO = '000012'


--Exame Normal



SELECT
  0        AS TAB_FL_QTDECH,
  0             AS RTP_FL_VALORCH,
  0             AS RTP_FL_VALORCHEXTERNO,
  0             AS RTP_FL_DESCONTO,
  0             AS RTP_FL_DESCONTOEXTERNO,
  0             AS RTP_FL_ACRESCIMO,
  EXD_ST_TABELA AS TAP_ST_CODIGO,
  REG.REG_CH_OBRIGATORIONUMGUIA,

  EXD_ST_CODIGO           AS TAB_ST_CODIGO,
  ''                      AS TAP_ST_DESCRICAO,
  EDI.EDI_FL_VALOR        AS ETP_FL_VALORDIFENCIADO,
  EDI.EDI_FL_VALOREXTERNO AS ETP_FL_VALORDIFENCIADOEXTERNO,
  EDI.EDI_FL_DESCONTO,
  EDI.EDI_FL_DESCONTOEXTERNO,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMESCOMAUTORIZACAO
  WHERE UNI_ST_CODIGO = EDI.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = EDI.CON_ST_CODIGO
  AND REG_ST_CODIGO   = EDI.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = EDI.EXA_ST_CODIGO
  ) AS ETP_ch_comautorizacao,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMENAOAUTORIZADOS
  WHERE UNI_ST_CODIGO = EDI.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = EDI.CON_ST_CODIGO
  AND REG_ST_CODIGO   = EDI.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = EDI.EXA_ST_CODIGO
  ) AS ETP_ch_naoautorizado,
  MAT.MAT_ST_CODIGO,
  MAT.MAT_ST_DESCRICAO,
  EXA.EXA_CH_OBRIGATORIOPESO,
  EXA.EXA_CH_OBRIGATORIOALTURA,
  EXA.EXA_CH_OBRIGAGESTANTE,
  EXA.EXA_CH_SEXO,
  EXU.MPA_ST_CODIGO,
  EXA.EXA_ST_DESCRICAO,
  EXA.EXA_CH_GRUPO,
  EXU.MET_ST_CODIGO,
  MET.MET_ST_DESCRICAO,
  EXM.EXM_DT_VALIDADE,
  EXU.EXU_CH_STATUS,
  EXU.EXU_CH_FATURA,
  EXA.EXA_CH_INATIVO,
  EXU.EXU_CH_INATIVO,
  EXU.EXU_CH_PREPARO,
  EXU.EXU_CH_obrigamaterial,
  EXU.PRA_ST_CODIGO,
  PRA.PRA_IN_DIAS,
  PRA.PRA_CH_DOMINGO,
  PRA.PRA_CH_SEGUNDA,
  PRA.PRA_CH_TERCA,
  PRA.PRA_CH_QUARTA,
  PRA.PRA_CH_QUINTA,
  PRA.PRA_CH_SEXTA,
  PRA.PRA_CH_SABADO,
  PRA.PRA_HR_FINAL,
  PRA.PRA_IN_HORAS    AS pra_in_horas,
  PRA1.PRA_IN_HORAS   AS pra_in_horasU,
  PRA1.PRA_IN_DIAS    AS pra_in_diasU,
  PRA1.PRA_CH_SEGUNDA AS pra_ch_segundaU,
  PRA1.PRA_CH_TERCA   AS pra_ch_tercaU,
  PRA1.PRA_CH_QUARTA  AS pra_ch_quartaU,
  PRA1.PRA_CH_QUINTA  AS pra_ch_quintaU,
  PRA1.PRA_CH_SEXTA   AS pra_ch_sextaU,
  PRA1.PRA_CH_SABADO  AS pra_ch_sabadoU,
  PRA1.PRA_CH_DOMINGO AS pra_ch_domingoU,
  PRA1.PRA_HR_FINAL   AS PRA_HR_FINALU,
  EXU.EXU_ST_PRAZOURGENTE,
  EXU.EXU_CH_NAONECESSARIO,
  ORI.ORI_ST_CODIGO,
  ORI.ORI_CH_URGENCIA,
  ORI.LEG_ST_CODIGOINICIAL,
  ORI.ORI_CH_CONFEREAUTOMATICO,
  ORI.ORI_CH_CONFEREAUTOMATICOFAT,
  EXU.SET_ST_CODIGO,
  EXU.EXU_ST_UNIDADEEX,
  EXU.EXU_ST_UNIDADEEXURGENTE,
  EXU.EXU_CH_CONFEREAUTOMATICOFAT,
  (SELECT MAX(EXA_ST_CODIGO)
  FROM LAB_EXAMEDEPENDENTE
  WHERE UNI_ST_CODIGO ='TTT'
  AND EXA_ST_CODIGO   ='TRI'
  ) AS DEP_ST_CODIGO
FROM LAB_EXAMESDIFERENCIADOS EDI,
  LAB_REGRAS REG,
  LAB_EXAMEUNIDADE EXU,
  LAB_ORIGEM ORI,
  LAB_PRAZO PRA1,
  LAB_PRAZO PRA,
  LAB_EXAME EXA,
  LAB_EXAMEMETODO EXM,
  LAB_METODO MET,
  LAB_MATERIAL MAT
WHERE EDI.UNI_ST_CODIGO     = REG.UNI_ST_CODIGO
AND EDI.CON_ST_CODIGO       = REG.CON_ST_CODIGO
AND EDI.REG_ST_CODIGO       = REG.REG_ST_CODIGO
AND EDI.UNI_ST_CODIGO       = EXU.UNI_ST_CODIGO
AND EDI.EXA_ST_CODIGO       = EXU.EXA_ST_CODIGO
AND EDI.UNI_ST_CODIGO       = ORI.UNI_ST_CODIGO
AND EXU.EXA_ST_CODIGO       = EXA.EXA_ST_CODIGO
AND EXU.EXU_ST_PRAZOURGENTE = PRA1.PRA_ST_CODIGO(+)
AND EXU.PRA_ST_CODIGO       = PRA.PRA_ST_CODIGO(+)
AND EXU.EXA_ST_CODIGO       = EXM.EXA_ST_CODIGO(+)
AND EXU.MET_ST_CODIGO       = EXM.MET_ST_CODIGO(+)
AND EXU.MET_ST_CODIGO       = MET.MET_ST_CODIGO(+)
AND EXA.MAT_ST_COLETA       = MAT.MAT_ST_CODIGO(+)
AND EDI.UNI_ST_CODIGO       ='TTT'
AND ORI.ORI_ST_CODIGO       ='000001'
AND EDI.CON_ST_CODIGO       ='1'
AND EDI.REG_ST_CODIGO       ='434'
AND EDI.EXA_ST_CODIGO       ='TRI'
AND EDI_DT_INICIOVALIDADE   =
  (SELECT MAX(EDI_DT_INICIOVALIDADE)
  FROM LAB_EXAMESDIFERENCIADOS
  WHERE UNI_ST_CODIGO        ='TTT'
  AND CON_ST_CODIGO          ='1'
  AND REG_ST_CODIGO          ='434'
  AND EXA_ST_CODIGO          ='TRI'
  AND EDI_DT_INICIOVALIDADE <= TO_DATE('2010-01-05','YYYY-MM-DD')
  )

UNION ALL

SELECT TAB1.TAB_FL_QTDECH,
  RTP1.rtp_fl_valorch,
  RTP1.RTP_FL_VALORCHEXTERNO,
  RTP1.rtp_fl_desconto,
  RTP1.rtp_fl_descontoEXTERNO,
  RTP1.rtp_fl_acrescimo,
  RTP1.TAP_ST_CODIGO,
  REG1.REG_CH_OBRIGATORIONUMGUIA,
  TAB1.TAB_ST_CODIGO,
  TAP1.tap_st_descricao,
  0 AS ETP_fl_valordifenciado,
  0 AS ETP_fl_valordifenciadoExterno,
  0 AS EDI_FL_DESCONTO,
  0 AS EDI_FL_DESCONTOEXTERNO,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMESCOMAUTORIZACAO
  WHERE UNI_ST_CODIGO = RTP1.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = RTP1.CON_ST_CODIGO
  AND REG_ST_CODIGO   = RTP1.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = ETP1.EXA_ST_CODIGO
  ) AS ETP_ch_comautorizacao,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMENAOAUTORIZADOS
  WHERE UNI_ST_CODIGO = RTP1.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = RTP1.CON_ST_CODIGO
  AND REG_ST_CODIGO   = RTP1.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = ETP1.EXA_ST_CODIGO
  ) AS ETP_ch_naoautorizado,
  MAT.MAT_ST_CODIGO,
  MAT.MAT_ST_DESCRICAO,
  EXA.EXA_CH_OBRIGATORIOPESO,
  EXA.EXA_CH_OBRIGATORIOALTURA,
  EXA.EXA_CH_OBRIGAGESTANTE,
  EXA.EXA_CH_SEXO,
  EXU.MPA_ST_CODIGO,
  EXA.EXA_ST_DESCRICAO,
  EXA.EXA_CH_GRUPO,
  EXU.MET_ST_CODIGO,
  MET.MET_ST_DESCRICAO,
  EXM.EXM_DT_VALIDADE,
  EXU.EXU_CH_STATUS,
  EXU.EXU_CH_FATURA,
  EXA.EXA_CH_INATIVO,
  EXU.EXU_CH_INATIVO,
  EXU.EXU_CH_PREPARO,
  EXU.EXU_CH_obrigamaterial,
  EXU.PRA_ST_CODIGO,
  PRA.PRA_IN_DIAS,
  PRA.PRA_CH_DOMINGO,
  PRA.PRA_CH_SEGUNDA,
  PRA.PRA_CH_TERCA,
  PRA.PRA_CH_QUARTA,
  PRA.PRA_CH_QUINTA,
  PRA.PRA_CH_SEXTA,
  PRA.PRA_CH_SABADO,
  PRA.PRA_HR_FINAL,
  PRA.PRA_IN_HORAS    AS pra_in_horas,
  PRA1.PRA_IN_HORAS   AS pra_in_horasU,
  PRA1.PRA_IN_DIAS    AS pra_in_diasU,
  PRA1.PRA_CH_SEGUNDA AS pra_ch_segundaU,
  PRA1.PRA_CH_TERCA   AS pra_ch_tercaU,
  PRA1.PRA_CH_QUARTA  AS pra_ch_quartaU,
  PRA1.PRA_CH_QUINTA  AS pra_ch_quintaU,
  PRA1.PRA_CH_SEXTA   AS pra_ch_sextaU,
  PRA1.PRA_CH_SABADO  AS pra_ch_sabadoU,
  PRA1.PRA_CH_DOMINGO AS pra_ch_domingoU,
  PRA1.PRA_HR_FINAL   AS PRA_HR_FINALU,
  EXU.EXU_ST_PRAZOURGENTE,
  EXU.EXU_CH_NAONECESSARIO,
  ORI.ORI_ST_CODIGO,
  ORI.ORI_CH_URGENCIA,
  ORI.LEG_ST_CODIGOINICIAL,
  ORI_CH_CONFEREAUTOMATICO,
  ORI_CH_CONFEREAUTOMATICOFAT,
  EXU.SET_ST_CODIGO,
  EXU.EXU_ST_UNIDADEEX,
  EXU.EXU_ST_UNIDADEEXURGENTE,
  EXU.EXU_CH_CONFEREAUTOMATICOFAT,
  (SELECT MAX(EXA_ST_CODIGO)
  FROM LAB_EXAMEDEPENDENTE
  WHERE UNI_ST_CODIGO ='TTT'
  AND EXA_ST_CODIGO   ='TRI'
  ) AS DEP_ST_CODIGO
FROM LAB_EXAME EXA,
  LAB_EXAMEUNIDADE EXU,
  LAB_METODO MET,
  LAB_MATERIAL MAT,
  LAB_EXAMEMETODO EXM,
  LAB_PRAZO PRA,
  LAB_PRAZO PRA1,
  LAB_ORIGEM ORI,
  LAB_EXAMETABELAPRECO ETP1,
  LAB_TABELA TAB1,
  LAB_REGRATABELAPRECO RTP1,
  LAB_REGRAS REG1,
  LAB_TABELAPRECO TAP1
WHERE EXU.EXA_ST_CODIGO        = EXA.EXA_ST_CODIGO
AND ORI.UNI_ST_CODIGO          = EXU.UNI_ST_CODIGO
AND EXU.EXU_ST_PRAZOURGENTE    = PRA1.PRA_ST_CODIGO(+)
AND EXU.MET_ST_CODIGO          = MET.MET_ST_CODIGO(+)
AND EXA.MAT_ST_COLETA          = MAT.MAT_ST_CODIGO(+)
AND EXU.PRA_ST_CODIGO          = PRA.PRA_ST_CODIGO(+)
AND EXU.EXA_ST_CODIGO          = EXM.EXA_ST_CODIGO(+)
AND EXU.MET_ST_CODIGO          = EXM.MET_ST_CODIGO(+)
AND REG1.uni_st_codigo         = RTP1.uni_st_codigo
AND REG1.con_st_codigo         = RTP1.con_st_codigo
AND REG1.reg_st_codigo         = RTP1.reg_st_codigo
AND ETP1.tap_st_codigo         = TAB1.tap_st_codigo
AND ETP1.tab_st_codigo         = TAB1.tab_st_codigo
AND TAB1.TAB_CH_TIPO           = '1'
AND TAB1.TAB_DT_INICIOVALIDADE =
  (SELECT MAX(TAB_DT_INICIOVALIDADE)
  FROM LAB_TABELA
  WHERE tap_st_codigo        = TAB1.tap_st_codigo
  AND tab_st_codigo          = TAB1.tab_st_codigo
  AND TAB_CH_TIPO            = '1'
  AND TAB_DT_INICIOVALIDADE <= TO_DATE('2010-01-05','YYYY-MM-DD')
  )
AND ETP1.tap_st_codigo         = RTP1.tap_st_codigo
AND TAB1.tap_st_codigo         = TAP1.tap_st_codigo
AND RTP1.uni_st_codigo         = EXU.UNI_ST_CODIGO
AND ETP1.exa_st_codigo         = EXA.EXA_ST_CODIGO
AND RTP1.RTP_DT_INICIOVALIDADE =
  (SELECT MAX(RTP_DT_INICIOVALIDADE)
  FROM LAB_REGRATABELAPRECO
  WHERE uni_st_codigo        = RTP1.uni_st_codigo
  AND con_st_codigo          = RTP1.con_st_codigo
  AND reg_st_codigo          = RTP1.reg_st_codigo
  AND RTP_DT_INICIOVALIDADE <= SYSDATE
  AND RTP1.RTP_IN_SEQUENCIA  =
    (SELECT MIN(RTP2.RTP_IN_SEQUENCIA)
    FROM LAB_EXAMETABELAPRECO ETP2,
      LAB_TABELA TAB2,
      LAB_REGRATABELAPRECO RTP2,
      LAB_TABELAPRECO TAP2
    WHERE ETP2.tap_st_codigo       = TAB2.tap_st_codigo
    AND ETP2.tab_st_codigo         = TAB2.tab_st_codigo
    AND TAB2.TAB_CH_TIPO           = '1'
    AND RTP2.uni_st_codigo         = RTP2.uni_st_codigo
    AND RTP2.con_st_codigo         = RTP2.con_st_codigo
    AND RTP2.reg_st_codigo         = RTP2.reg_st_codigo
    AND ETP2.tap_st_codigo         = RTP2.tap_st_codigo
    AND TAB2.tap_st_codigo         = TAP2.tap_st_codigo
    AND RTP2.uni_st_codigo         = RTP1.UNI_ST_CODIGO
    AND RTP2.con_st_codigo         = RTP1.CON_ST_CODIGO
    AND RTP2.reg_st_codigo         = RTP1.REG_ST_CODIGO
    AND ETP2.TAP_ST_CODIGO         = ETP2.TAP_ST_CODIGO
    AND ETP2.exa_st_codigo         = ETP1.EXA_ST_CODIGO
    AND RTP2.RTP_DT_INICIOVALIDADE =
      (SELECT MAX(RTP_DT_INICIOVALIDADE)
      FROM LAB_REGRATABELAPRECO
      WHERE uni_st_codigo       = RTP1.uni_st_codigo
      AND con_st_codigo         = RTP1.con_st_codigo
      AND reg_st_codigo         = RTP1.reg_st_codigo
      AND tap_st_codigo         = ETP1.tap_st_codigo
      AND RTP_DT_INICIOVALIDADE = RTP1.Rtp_Dt_Iniciovalidade
      )
    )
  )
AND EXU.UNI_ST_CODIGO  ='TTT'
AND RTP1.CON_ST_CODIGO ='1'
AND RTP1.REG_ST_CODIGO ='434'
AND ORI.ORI_ST_CODIGO  ='000001'
AND EXA.EXA_ST_CODIGO  ='TRI'


/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/



SELECT DEP.DEP_ST_CODIGO  FROM  LAB_EXAMEDEPENDENTE DEP,LAB_EXAME EXA
                                   WHERE DEP.UNI_ST_CODIGO = 'TTT'
                                         AND DEP.EXA_ST_CODIGO = 'TRI'
                                         AND DEP.EXA_ST_CODIGO = EXA.EXA_ST_CODIGO
                                         AND EXA.EXA_CH_GRUPO = 'S'


/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/





-- Grupo

SELECT
 0        AS TAB_FL_QTDECH,
  0             AS RTP_FL_VALORCH,
  0             AS RTP_FL_VALORCHEXTERNO,
  0             AS RTP_FL_DESCONTO,
  0             AS RTP_FL_DESCONTOEXTERNO,
  0             AS RTP_FL_ACRESCIMO,
  EXD_ST_TABELA AS TAP_ST_CODIGO,
  REG.REG_CH_OBRIGATORIONUMGUIA,
  EXD_ST_CODIGO           AS TAB_ST_CODIGO,
  ''                      AS TAP_ST_DESCRICAO,
  EDI.EDI_FL_VALOR        AS ETP_FL_VALORDIFENCIADO,
  EDI.EDI_FL_VALOREXTERNO AS ETP_FL_VALORDIFENCIADOEXTERNO,
  EDI.EDI_FL_DESCONTO,
  EDI.EDI_FL_DESCONTOEXTERNO,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMESCOMAUTORIZACAO
  WHERE UNI_ST_CODIGO = EDI.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = EDI.CON_ST_CODIGO
  AND REG_ST_CODIGO   = EDI.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = EDI.EXA_ST_CODIGO
  ) AS ETP_ch_comautorizacao,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMENAOAUTORIZADOS
  WHERE UNI_ST_CODIGO = EDI.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = EDI.CON_ST_CODIGO
  AND REG_ST_CODIGO   = EDI.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = EDI.EXA_ST_CODIGO
  ) AS ETP_ch_naoautorizado,
  (SELECT EXA_CH_OBRIGAGESTANTE FROM LAB_EXAME WHERE EXA_ST_CODIGO= 'COAG'
  ) AS EXA_CH_OBRIGAGESTANTE,
  (SELECT EXA_CH_SEXO FROM LAB_EXAME WHERE EXA_ST_CODIGO= 'COAG'
  ) AS EXA_CH_SEXO,
  MAT.MAT_ST_CODIGO,
  MAT.MAT_ST_DESCRICAO,
  EXG.EXA_ST_CODIGO,
  EXA.EXA_CH_OBRIGATORIOPESO,
  EXA.EXA_ST_DESCRICAO,
  EXA.EXA_CH_OBRIGATORIOALTURA,
  EXA.EXA_CH_OBRIGAGESTANTE,
  EXA.EXA_CH_SEXO,
  EXA.EXA_CH_GRUPO,
  EXA.EXA_CH_INATIVO,
  EXU.EXU_CH_PREPARO,
  EXU.MPA_ST_CODIGO,
  EXU.MET_ST_CODIGO,
  EXU.EXU_CH_obrigamaterial,
  EXU.PRA_ST_CODIGO,
  EXU.EXU_ST_PRAZOURGENTE,
  EXU.EXU_ST_UNIDADEEX,
  EXU.SET_ST_CODIGO,
  EXU.EXU_ST_UNIDADEEXURGENTE,
  EXU.EXU_CH_CONFEREAUTOMATICOFAT,
  PRA.PRA_IN_DIAS,
  PRA.PRA_IN_HORAS AS pra_in_horas,
  PRA.PRA_CH_DOMINGO,
  PRA.PRA_CH_SEGUNDA,
  PRA.PRA_CH_TERCA,
  PRA.PRA_CH_QUARTA,
  PRA.PRA_CH_QUINTA,
  PRA.PRA_CH_SEXTA,
  PRA.PRA_CH_SABADO,
  PRA.PRA_HR_FINAL,
  PRA1.PRA_IN_HORAS   AS pra_in_horasU,
  PRA1.PRA_IN_DIAS    AS pra_in_diasU,
  PRA1.PRA_CH_SEGUNDA AS pra_ch_segundaU,
  PRA1.PRA_CH_TERCA   AS pra_ch_tercaU,
  PRA1.PRA_CH_QUARTA  AS pra_ch_quartaU,
  PRA1.PRA_CH_QUINTA  AS pra_ch_quintaU,
  PRA1.PRA_CH_SEXTA   AS pra_ch_sextaU,
  PRA1.PRA_CH_SABADO  AS pra_ch_sabadoU,
  PRA1.PRA_CH_DOMINGO AS pra_ch_domingoU,
  PRA1.PRA_HR_FINAL   AS PRA_HR_FINALU,
  MET.MET_ST_DESCRICAO,
  ORI.ORI_ST_CODIGO,
  ORI.ORI_CH_URGENCIA,
  ORI.LEG_ST_CODIGOINICIAL,
  ORI.ORI_CH_CONFEREAUTOMATICO,
  ORI.ORI_CH_CONFEREAUTOMATICOFAT,
  EXM.EXM_DT_VALIDADE,
  CASE
    WHEN (EXU.EXU_CH_NAONECESSARIO = 'S'
    OR EXG.EXG_CH_OMITE            = 'S')
    THEN 'S'
    ELSE 'N'
  END AS EXU_CH_NAONECESSARIO,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMENAOAUTORIZADOS
  WHERE UNI_ST_CODIGO = EDI.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = EDI.CON_ST_CODIGO
  AND REG_ST_CODIGO   = EDI.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = EDI.EXA_ST_CODIGO
  ) AS ETP_ch_naoautorizadoG,
  EXU.EXU_CH_SOFATURAMENTO,
  EXU.EXU_CH_FATURA,
  EXG.SEQ_ST_CODIGO,
  EXG.EXG_CH_FATURA,
  SEQ.SEQ_ST_DESCRICAO,
  (SELECT EXMG.EXM_DT_VALIDADE
  FROM LAB_EXAMEMETODO EXMG
  WHERE EXMG.EXA_ST_CODIGO ='COAG'
  AND EXMG.MET_ST_CODIGO   =
    (SELECT MET_ST_CODIGO
    FROM LAB_EXAMEUNIDADE
    WHERE UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
    AND EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
    )
  ) AS EXM_DT_VALIDADEGRUPO,
  (SELECT EXUG.EXU_CH_FATURAGRUPO
  FROM LAB_EXAMEUNIDADE EXUG
  WHERE EXUG.UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
  AND EXUG.EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
  ) AS EXU_CH_FATURAGRUPO,
  (SELECT EXAG1.EXA_ST_DESCRICAO
  FROM LAB_EXAME EXAG1
  WHERE EXAG1.EXA_ST_CODIGO = EXG.EXA_ST_CODIGO
  ) AS EXA_ST_DESCRICAOGRUPO,
  (SELECT EXUG.EXU_CH_INATIVO
  FROM LAB_EXAMEUNIDADE EXUG
  WHERE EXUG.UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
  AND EXUG.EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
  ) AS EXU_CH_INATIVOG,
  (SELECT MET_ST_CODIGO
  FROM LAB_EXAMEUNIDADE
  WHERE UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
  AND EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
  )        AS MET_ST_CODIGOGRP,
  '      ' AS UNIDADEEX
FROM LAB_EXAMESDIFERENCIADOS EDI,
  LAB_REGRAS REG,
  LAB_EXAMEUNIDADE EXU,
  LAB_ORIGEM ORI,
  LAB_PRAZO PRA1,
  LAB_PRAZO PRA,
  LAB_EXAME EXA,
  LAB_EXAMEMETODO EXM,
  LAB_METODO MET,
  LAB_SEQUENCIAAMOSTRA SEQ,
  LAB_MATERIAL MAT,
  LAB_EXAMEGRUPO EXG
WHERE EDI.UNI_ST_CODIGO     = REG.UNI_ST_CODIGO
AND EDI.CON_ST_CODIGO       = REG.CON_ST_CODIGO
AND EDI.REG_ST_CODIGO       = REG.REG_ST_CODIGO
AND EDI.UNI_ST_CODIGO       = EXU.UNI_ST_CODIGO
AND EDI.EXA_ST_CODIGO       = EXU.EXA_ST_CODIGO
AND EDI.UNI_ST_CODIGO       = ORI.UNI_ST_CODIGO
AND EXU.EXA_ST_CODIGO       = EXA.EXA_ST_CODIGO
AND EXU.EXU_ST_PRAZOURGENTE = PRA1.PRA_ST_CODIGO(+)
AND EXU.PRA_ST_CODIGO       = PRA.PRA_ST_CODIGO(+)
AND EXU.EXA_ST_CODIGO       = EXM.EXA_ST_CODIGO(+)
AND EXU.MET_ST_CODIGO       = EXM.MET_ST_CODIGO(+)
AND EXU.MET_ST_CODIGO       = MET.MET_ST_CODIGO(+)
AND EXA.MAT_ST_COLETA       = MAT.MAT_ST_CODIGO(+)
AND EXG.SEQ_ST_CODIGO       = SEQ.SEQ_ST_CODIGO (+)
AND EXG.UNI_ST_CODIGO       = EDI.UNI_ST_CODIGO (+)
AND EXG.EXA_ST_CODIGO       = EDI.EXA_ST_CODIGO (+)
AND EXG.UNI_ST_CODIGO       ='TTT'
AND EXG.GRP_ST_CODIGO       ='COAG'
AND ORI.ORI_ST_CODIGO       ='000001'
AND EDI.CON_ST_CODIGO       ='1'
AND EDI.REG_ST_CODIGO       ='434'
AND EDI_DT_INICIOVALIDADE   =
  (SELECT MAX(EDI_DT_INICIOVALIDADE)
  FROM LAB_EXAMESDIFERENCIADOS
  WHERE UNI_ST_CODIGO        = EDI.UNI_ST_CODIGO
  AND CON_ST_CODIGO          = EDI.CON_ST_CODIGO
  AND REG_ST_CODIGO          = EDI.REG_ST_CODIGO
  AND EXA_ST_CODIGO          = EDI.EXA_ST_CODIGO
  AND EDI_DT_INICIOVALIDADE <= TO_DATE ('2010/01/01','YYYY-MM-DD')  /*  ('+#39 + cData_InicioValidade + #39+','+Quotedstr(YYYY-MM-DD) */
  )

UNION ALL

SELECT TAB1.TAB_FL_QTDECH,
  RTP1.rtp_fl_valorch,
  RTP1.RTP_FL_VALORCHEXTERNO,
  RTP1.rtp_fl_desconto,
  RTP1.rtp_fl_descontoEXTERNO,
  RTP1.rtp_fl_acrescimo,
  RTP1.TAP_ST_CODIGO,
  REG1.REG_CH_OBRIGATORIONUMGUIA,
  TAB1.TAB_ST_CODIGO,
  TAP1.tap_st_descricao,
  0 AS ETP_fl_valordifenciado,
  0 AS ETP_fl_valordifenciadoExterno,
  0 AS EDI_FL_DESCONTO,
  0 AS EDI_FL_DESCONTOEXTERNO,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMESCOMAUTORIZACAO
  WHERE UNI_ST_CODIGO = RTP1.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = RTP1.CON_ST_CODIGO
  AND REG_ST_CODIGO   = RTP1.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = ETP1.EXA_ST_CODIGO
  ) AS ETP_ch_comautorizacao,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMENAOAUTORIZADOS
  WHERE UNI_ST_CODIGO = RTP1.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = RTP1.CON_ST_CODIGO
  AND REG_ST_CODIGO   = RTP1.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = ETP1.EXA_ST_CODIGO
  ) AS ETP_ch_naoautorizado,
  (SELECT EXA_CH_OBRIGAGESTANTE FROM LAB_EXAME WHERE EXA_ST_CODIGO='COAG'
  ) AS EXA_CH_OBRIGAGESTANTE,
  (SELECT EXA_CH_SEXO FROM LAB_EXAME WHERE EXA_ST_CODIGO='COAG'
  ) AS EXA_CH_SEXO,
  MAT.MAT_ST_CODIGO,
  MAT.MAT_ST_DESCRICAO,
  EXA.EXA_ST_CODIGO,
  EXA.EXA_CH_OBRIGATORIOPESO,
  EXA.EXA_ST_DESCRICAO,
  EXA.EXA_CH_OBRIGATORIOALTURA,
  EXA.EXA_CH_OBRIGAGESTANTE,
  EXA.EXA_CH_SEXO,
  EXA.EXA_CH_GRUPO,
  EXA.EXA_CH_INATIVO,
  EXU.EXU_CH_PREPARO,
  EXU.MPA_ST_CODIGO,
  EXU.MET_ST_CODIGO,
  EXU.EXU_CH_obrigamaterial,
  EXU.PRA_ST_CODIGO,
  EXU.EXU_ST_PRAZOURGENTE,
  EXU.EXU_ST_UNIDADEEX,
  EXU.SET_ST_CODIGO,
  EXU.EXU_ST_UNIDADEEXURGENTE,
  EXU.EXU_CH_CONFEREAUTOMATICOFAT,
  PRA.PRA_IN_DIAS,
  PRA.PRA_IN_HORAS AS pra_in_horas,
  PRA.PRA_CH_DOMINGO,
  PRA.PRA_CH_SEGUNDA,
  PRA.PRA_CH_TERCA,
  PRA.PRA_CH_QUARTA,
  PRA.PRA_CH_QUINTA,
  PRA.PRA_CH_SEXTA,
  PRA.PRA_CH_SABADO,
  PRA.PRA_HR_FINAL,
  PRA1.PRA_IN_HORAS   AS pra_in_horasU,
  PRA1.PRA_IN_DIAS    AS pra_in_diasU,
  PRA1.PRA_CH_SEGUNDA AS pra_ch_segundaU,
  PRA1.PRA_CH_TERCA   AS pra_ch_tercaU,
  PRA1.PRA_CH_QUARTA  AS pra_ch_quartaU,
  PRA1.PRA_CH_QUINTA  AS pra_ch_quintaU,
  PRA1.PRA_CH_SEXTA   AS pra_ch_sextaU,
  PRA1.PRA_CH_SABADO  AS pra_ch_sabadoU,
  PRA1.PRA_CH_DOMINGO AS pra_ch_domingoU,
  PRA1.PRA_HR_FINAL   AS PRA_HR_FINALU,
  MET.MET_ST_DESCRICAO,
  ORI.ORI_ST_CODIGO,
  ORI.ORI_CH_URGENCIA,
  ORI.LEG_ST_CODIGOINICIAL,
  ORI_CH_CONFEREAUTOMATICO,
  ORI_CH_CONFEREAUTOMATICOFAT,
  EXM.EXM_DT_VALIDADE,
  CASE
    WHEN (EXU.EXU_CH_NAONECESSARIO = 'S'
    OR EXG.EXG_CH_OMITE            = 'S')
    THEN 'S'
    ELSE 'N'
  END AS EXU_CH_NAONECESSARIO,
  (SELECT
    CASE
      WHEN COUNT(*) > 0
      THEN 'S'
      ELSE 'N'
    END
  FROM LAB_EXAMENAOAUTORIZADOS
  WHERE UNI_ST_CODIGO = RTP1.UNI_ST_CODIGO
  AND CON_ST_CODIGO   = RTP1.CON_ST_CODIGO
  AND REG_ST_CODIGO   = RTP1.REG_ST_CODIGO
  AND EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
  ) AS ETP_ch_naoautorizadoG,
  EXU.EXU_CH_SOFATURAMENTO,
  EXU.EXU_CH_FATURA,
  EXG.SEQ_ST_CODIGO,
  EXG.EXG_CH_FATURA,
  SEQ.SEQ_ST_DESCRICAO,
  (SELECT EXMG.EXM_DT_VALIDADE
  FROM LAB_EXAMEMETODO EXMG
  WHERE EXMG.EXA_ST_CODIGO ='COAG'
  AND EXMG.MET_ST_CODIGO   =
    (SELECT MET_ST_CODIGO
    FROM LAB_EXAMEUNIDADE
    WHERE UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
    AND EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
    )
  ) AS EXM_DT_VALIDADEGRUPO,
  (SELECT EXUG.EXU_CH_FATURAGRUPO
  FROM LAB_EXAMEUNIDADE EXUG
  WHERE EXUG.UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
  AND EXUG.EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
  ) AS EXU_CH_FATURAGRUPO,
  (SELECT EXAG1.EXA_ST_DESCRICAO
  FROM LAB_EXAME EXAG1
  WHERE EXAG1.EXA_ST_CODIGO = EXG.GRP_ST_CODIGO
  ) AS EXA_ST_DESCRICAOGRUPO,
  (SELECT EXUG.EXU_CH_INATIVO
  FROM LAB_EXAMEUNIDADE EXUG
  WHERE EXUG.UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
  AND EXUG.EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
  ) AS EXU_CH_INATIVOG,
  (SELECT MET_ST_CODIGO
  FROM LAB_EXAMEUNIDADE
  WHERE UNI_ST_CODIGO = EXG.UNI_ST_CODIGO
  AND EXA_ST_CODIGO   = EXG.GRP_ST_CODIGO
  )        AS MET_ST_CODIGOGRP,
  '      ' AS UNIDADEEX
FROM LAB_EXAMEGRUPO EXG,
  LAB_EXAME EXA,
  LAB_EXAMEUNIDADE EXU,
  LAB_METODO MET,
  LAB_MATERIAL MAT,
  LAB_EXAMEMETODO EXM,
  LAB_SEQUENCIAAMOSTRA SEQ,
  LAB_PRAZO PRA,
  LAB_PRAZO PRA1,
  LAB_ORIGEM ORI,
  LAB_EXAMETABELAPRECO ETP1,
  LAB_TABELA TAB1,
  LAB_REGRATABELAPRECO RTP1,
  LAB_TABELAPRECO TAP1,
  LAB_REGRAS REG1
WHERE EXG.EXA_ST_CODIGO        = EXA.EXA_ST_CODIGO
AND EXU.MET_ST_CODIGO          = MET.MET_ST_CODIGO
AND EXU.EXA_ST_CODIGO          = EXM.EXA_ST_CODIGO
AND EXU.MET_ST_CODIGO          = EXM.MET_ST_CODIGO
AND EXA.MAT_ST_COLETA          = MAT.MAT_ST_CODIGO
AND EXU.EXA_ST_CODIGO          = EXA.EXA_ST_CODIGO
AND EXU.PRA_ST_CODIGO          = PRA.PRA_ST_CODIGO(+)
AND EXU.EXU_ST_PRAZOURGENTE    = PRA1.PRA_ST_CODIGO(+)
AND EXG.SEQ_ST_CODIGO          = SEQ.SEQ_ST_CODIGO
AND ORI.UNI_ST_CODIGO          = EXU.UNI_ST_CODIGO
AND ETP1.tap_st_codigo         = TAB1.tap_st_codigo
AND ETP1.tab_st_codigo         = TAB1.tab_st_codigo
AND TAB1.TAB_CH_TIPO           = '1'
AND TAB1.TAB_DT_INICIOVALIDADE =
  (SELECT MAX(TAB_DT_INICIOVALIDADE)
  FROM LAB_TABELA
  WHERE tap_st_codigo        = TAB1.tap_st_codigo
  AND tab_st_codigo          = TAB1.tab_st_codigo
  AND TAB_CH_TIPO            = '1'
  AND TAB_DT_INICIOVALIDADE <= TO_DATE ('2010/01/01','YYYY-MM-DD') /**/
  )
AND REG1.uni_st_codigo         = RTP1.uni_st_codigo
AND REG1.con_st_codigo         = RTP1.con_st_codigo
AND REG1.reg_st_codigo         = RTP1.reg_st_codigo
AND ETP1.tap_st_codigo         = RTP1.tap_st_codigo
AND TAB1.tap_st_codigo         = TAP1.tap_st_codigo
AND RTP1.uni_st_codigo         = EXU.UNI_ST_CODIGO
AND ETP1.exa_st_codigo         = EXA.EXA_ST_CODIGO
AND RTP1.RTP_DT_INICIOVALIDADE =
  (SELECT MAX(RTP_DT_INICIOVALIDADE)
  FROM LAB_REGRATABELAPRECO
  WHERE uni_st_codigo        = RTP1.uni_st_codigo
  AND con_st_codigo          = RTP1.con_st_codigo
  AND reg_st_codigo          = RTP1.reg_st_codigo
  AND RTP_DT_INICIOVALIDADE <= SYSDATE
  AND RTP1.RTP_IN_SEQUENCIA  =
    (SELECT MIN(RTP2.RTP_IN_SEQUENCIA)
    FROM LAB_EXAMETABELAPRECO ETP2,
      LAB_TABELA TAB2,
      LAB_REGRATABELAPRECO RTP2,
      LAB_TABELAPRECO TAP2
    WHERE ETP2.tap_st_codigo       = TAB2.tap_st_codigo
    AND ETP2.tab_st_codigo         = TAB2.tab_st_codigo
    AND TAB2.TAB_CH_TIPO           = '1'
    AND RTP2.uni_st_codigo         = RTP2.uni_st_codigo
    AND RTP2.con_st_codigo         = RTP2.con_st_codigo
    AND RTP2.reg_st_codigo         = RTP2.reg_st_codigo
    AND ETP2.tap_st_codigo         = RTP2.tap_st_codigo
    AND TAB2.tap_st_codigo         = TAP2.tap_st_codigo
    AND RTP2.uni_st_codigo         = RTP1.UNI_ST_CODIGO
    AND RTP2.con_st_codigo         = RTP1.CON_ST_CODIGO
    AND RTP2.reg_st_codigo         = RTP1.REG_ST_CODIGO
    AND ETP2.TAP_ST_CODIGO         = ETP2.TAP_ST_CODIGO
    AND ETP2.exa_st_codigo         = ETP1.EXA_ST_CODIGO
    AND RTP2.RTP_DT_INICIOVALIDADE =
      (SELECT MAX(RTP_DT_INICIOVALIDADE)
      FROM LAB_REGRATABELAPRECO
      WHERE uni_st_codigo       = RTP1.uni_st_codigo
      AND con_st_codigo         = RTP1.con_st_codigo
      AND reg_st_codigo         = RTP1.reg_st_codigo
      AND tap_st_codigo         = ETP1.tap_st_codigo
      AND RTP_DT_INICIOVALIDADE = RTP1.Rtp_Dt_Iniciovalidade
      )
    )
  )
AND EXU.UNI_ST_CODIGO ='TTT'
AND EXG.UNI_ST_CODIGO ='TTT'
AND RTP1.CON_ST_CODIGO='1'
AND RTP1.REG_ST_CODIGO='434'
AND ORI.ORI_ST_CODIGO ='000001'
AND EXG.GRP_ST_CODIGO ='COAG'


--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


Dependente

SELECT
                         0 AS TAB_FL_QTDECH,
                         0 AS RTP_FL_VALORCH,
                         0 AS RTP_FL_VALORCHEXTERNO,
                         0 AS RTP_FL_DESCONTO,
                         0 AS RTP_FL_DESCONTOEXTERNO,
                         0 AS RTP_FL_ACRESCIMO,
                         EXD_ST_TABELA AS TAP_ST_CODIGO,
                         REG.REG_CH_OBRIGATORIONUMGUIA,
                         EXD_ST_CODIGO AS TAB_ST_CODIGO,
                         '' AS TAP_ST_DESCRICAO,
                         EDI.EDI_FL_VALOR AS ETP_FL_VALORDIFENCIADO,
                         EDI.EDI_FL_VALOREXTERNO AS ETP_FL_VALORDIFENCIADOEXTERNO,
                         EDI.EDI_FL_DESCONTO,
                         EDI.EDI_FL_DESCONTOEXTERNO,
                         'N' AS ETP_CH_COMAUTORIZACAO,
                         'N' AS ETP_CH_NAOAUTORIZADO,
                         der.exa_st_codigo,
                         dep.dep_st_codigo,
                         DEP.DEP_CH_OMITE,
                         dep.dep_ch_fatura,
                         MAT.MAT_ST_CODIGO,
                         MAT.MAT_ST_DESCRICAO,
                         EXA.EXA_CH_OBRIGATORIOPESO,
                         EXA.EXA_CH_OBRIGATORIOALTURA,
                         EXA.EXA_CH_OBRIGAGESTANTE,
                         EXA.EXA_CH_SEXO,
                         EXU.MPA_ST_CODIGO,
                         EXA.EXA_ST_DESCRICAO,
                         EXU.EXU_CH_obrigamaterial,
                         EXU.MET_ST_CODIGO,
                         MET.MET_ST_DESCRICAO,
                         EXM.EXM_DT_VALIDADE,
                         EXU.EXU_CH_STATUS,
                         EXU.EXU_CH_FATURA,
                         EXA.EXA_CH_INATIVO,
                         EXU.EXU_CH_PREPARO,
                         EXA.EXA_CH_GRUPO,
                         EXU.EXU_CH_INATIVO,
                         EXU.PRA_ST_CODIGO,
                         EXU.EXU_ST_UNIDADEEX,
                         EXU.EXU_ST_UNIDADEEXURGENTE,
                         EXU.EXU_CH_CONFEREAUTOMATICOFAT,
                         PRA.PRA_IN_DIAS,
                         PRA.PRA_CH_DOMINGO,
                         PRA.PRA_CH_SEGUNDA,
                         PRA.PRA_CH_TERCA,
                         PRA.PRA_CH_QUARTA,
                         PRA.PRA_CH_QUINTA,
                         PRA.PRA_CH_SEXTA,
                         PRA.PRA_CH_SABADO,
                         PRA.PRA_HR_FINAL,
                        EXU.EXU_ST_PRAZOURGENTE,
                         PRA.PRA_IN_HORAS as pra_in_horas,
                         PRA1.PRA_IN_HORAS as pra_in_horasU,
                         EXU.APO_ST_CODIGO,
                         PRA1.PRA_IN_DIAS as pra_in_diasU,
                         PRA1.PRA_CH_SEGUNDA as pra_ch_segundaU,
                         PRA1.PRA_CH_TERCA as pra_ch_tercaU,
                         PRA1.PRA_CH_QUARTA as pra_ch_quartaU,
                         PRA1.PRA_CH_QUINTA as pra_ch_quintaU,
                         PRA1.PRA_CH_SEXTA as pra_ch_sextaU,
                         PRA1.PRA_CH_SABADO as pra_ch_sabadoU,
                         PRA1.PRA_CH_DOMINGO as pra_ch_domingoU,
                         PRA1.PRA_HR_FINAL AS PRA_HR_FINALU,
                         ORI.ORI_ST_CODIGO,
                         ORI.ORI_CH_URGENCIA,
                         ORI.LEG_ST_CODIGOINICIAL,
                         ORI.ORI_CH_CONFEREAUTOMATICO,
                         ORI.ORI_CH_CONFEREAUTOMATICOFAT,
                         EXU.SET_ST_CODIGO,
                         '      ' as UNIDADEEX
                  FROM LAB_EXAMESDIFERENCIADOS EDI, LAB_REGRAS REG, LAB_EXAMEUNIDADE EXU, LAB_ORIGEM ORI,LAB_PRAZO PRA1,LAB_EXAMEDEPENDENTE DEP,
                       LAB_PRAZO PRA,LAB_EXAME EXA, LAB_EXAMEMETODO EXM,LAB_METODO MET,LAB_MATERIAL MAT,lab_detalherequisicao der
                   WHERE EDI.UNI_ST_CODIGO = REG.UNI_ST_CODIGO
                     AND EDI.CON_ST_CODIGO = REG.CON_ST_CODIGO
                     AND EDI.REG_ST_CODIGO = REG.REG_ST_CODIGO
                     AND EDI.UNI_ST_CODIGO = EXU.UNI_ST_CODIGO
                     AND EDI.EXA_ST_CODIGO = EXU.EXA_ST_CODIGO
                     AND DER.EXA_ST_CODIGO = DEP.EXA_ST_CODIGO
                     AND DER.UNI_ST_CODIGO = DEP.UNI_ST_CODIGO
                     AND EXA.EXA_ST_CODIGO = DEP.DEP_ST_CODIGO
                     AND EDI.UNI_ST_CODIGO = ORI.UNI_ST_CODIGO
                     AND EXU.EXA_ST_CODIGO = EXA.EXA_ST_CODIGO
                     AND EXU.EXU_ST_PRAZOURGENTE = PRA1.PRA_ST_CODIGO(+)
                     AND EXU.PRA_ST_CODIGO = PRA.PRA_ST_CODIGO(+)
                     AND EXU.EXA_ST_CODIGO = EXM.EXA_ST_CODIGO(+)
                     AND EXU.MET_ST_CODIGO = EXM.MET_ST_CODIGO(+)
                     AND EXU.MET_ST_CODIGO = MET.MET_ST_CODIGO(+)
                     AND EXA.MAT_ST_COLETA = MAT.MAT_ST_CODIGO(+)
                     AND EDI.UNI_ST_CODIGO ='TTT'
                     AND ORI.ORI_ST_CODIGO ='000001'
                     AND EDI.CON_ST_CODIGO ='1'
                     AND EDI.REG_ST_CODIGO ='434'
                     and DER.REQ_ST_CODIGO ='1024625884'
                     AND EDI_DT_INICIOVALIDADE =
                         (SELECT MAX(EDI_DT_INICIOVALIDADE) FROM LAB_EXAMESDIFERENCIADOS
                           WHERE UNI_ST_CODIGO = EDI.UNI_ST_CODIGO
                             AND CON_ST_CODIGO = EDI.CON_ST_CODIGO
                             AND REG_ST_CODIGO = EDI.REG_ST_CODIGO
                             AND EXA_ST_CODIGO = EDI.EXA_ST_CODIGO
                             AND EDI_DT_INICIOVALIDADE <= TO_DATE('2010-01-05','YYYY-MM-DD'))
                  UNION ALL
                  select TAB1.TAB_FL_QTDECH,
                         RTP1.rtp_fl_valorch,
                         RTP1.rtp_fl_valorchEXTERNO,
                         RTP1.rtp_fl_desconto,
                         RTP1.rtp_fl_descontoEXTERNO,
                         RTP1.rtp_fl_acrescimo,
                         RTP1.TAP_ST_CODIGO,
                         REG1.REG_CH_OBRIGATORIONUMGUIA,
                         TAB1.TAB_ST_CODIGO,
                         TAP1.tap_st_descricao,
                         0 AS ETP_fl_valordifenciado,
                         0 AS ETP_fl_valordifenciadoExterno,
                         0 EDI_FL_DESCONTO,
                         0 EDI_FL_DESCONTOEXTERNO,
                         (SELECT CASE WHEN COUNT(*) > 0 THEN  'S'  ELSE  'N'  END
                            FROM LAB_EXAMESCOMAUTORIZACAO
                           WHERE UNI_ST_CODIGO = RTP1.UNI_ST_CODIGO
                             AND CON_ST_CODIGO = RTP1.CON_ST_CODIGO
                             AND REG_ST_CODIGO = RTP1.REG_ST_CODIGO
                             AND EXA_ST_CODIGO = ETP1.EXA_ST_CODIGO) AS ETP_ch_comautorizacao,
                         (SELECT CASE WHEN COUNT(*) > 0 THEN 'S' ELSE 'N' END FROM LAB_EXAMENAOAUTORIZADOS
                           WHERE UNI_ST_CODIGO = RTP1.UNI_ST_CODIGO
                             AND CON_ST_CODIGO = RTP1.CON_ST_CODIGO
                             AND REG_ST_CODIGO = RTP1.REG_ST_CODIGO
                             AND EXA_ST_CODIGO = ETP1.EXA_ST_CODIGO) AS ETP_ch_naoautorizado,
                         der.exa_st_codigo,
                         dep.dep_st_codigo,
                         DEP.DEP_CH_OMITE,
                         dep.dep_ch_fatura,
                         MAT.MAT_ST_CODIGO,
                         MAT.MAT_ST_DESCRICAO,
                         EXA.EXA_CH_OBRIGATORIOPESO,
                         EXA.EXA_CH_OBRIGATORIOALTURA,
                         EXA.EXA_CH_OBRIGAGESTANTE,
                         EXA.EXA_CH_SEXO,
                         EXU.MPA_ST_CODIGO,
                         EXA.EXA_ST_DESCRICAO,
                         EXU.EXU_CH_obrigamaterial,
                         EXU.MET_ST_CODIGO,
                         MET.MET_ST_DESCRICAO,
                         EXM.EXM_DT_VALIDADE,
                         EXU.EXU_CH_STATUS,
                         EXU.EXU_CH_FATURA,
                         EXA.EXA_CH_INATIVO,
                         EXU.EXU_CH_PREPARO,
                         EXA.EXA_CH_GRUPO,
                         EXU.EXU_CH_INATIVO,
                         EXU.PRA_ST_CODIGO,
                         EXU.EXU_ST_UNIDADEEX,
                         EXU.EXU_ST_UNIDADEEXURGENTE,
                         EXU.EXU_CH_CONFEREAUTOMATICOFAT,
                         PRA.PRA_IN_DIAS,
                         PRA.PRA_CH_DOMINGO,
                         PRA.PRA_CH_SEGUNDA,
                         PRA.PRA_CH_TERCA,
                         PRA.PRA_CH_QUARTA,
                         PRA.PRA_CH_QUINTA,
                         PRA.PRA_CH_SEXTA,
                         PRA.PRA_CH_SABADO,
                         PRA.PRA_HR_FINAL,
                         EXU.EXU_ST_PRAZOURGENTE,
                         PRA.PRA_IN_HORAS as pra_in_horas,
                         PRA1.PRA_IN_HORAS as pra_in_horasU,
                         EXU.APO_ST_CODIGO,
                         PRA1.PRA_IN_DIAS as pra_in_diasU,
                         PRA1.PRA_CH_SEGUNDA as pra_ch_segundaU,
                         PRA1.PRA_CH_TERCA as pra_ch_tercaU,
                         PRA1.PRA_CH_QUARTA as pra_ch_quartaU,
                         PRA1.PRA_CH_QUINTA as pra_ch_quintaU,
                         PRA1.PRA_CH_SEXTA as pra_ch_sextaU,
                         PRA1.PRA_CH_SABADO as pra_ch_sabadoU,
                         PRA1.PRA_CH_DOMINGO as pra_ch_domingoU,
                         PRA1.PRA_HR_FINAL AS PRA_HR_FINALU,
                         ORI.ORI_ST_CODIGO,
                         ORI.ORI_CH_URGENCIA,
                         ORI.LEG_ST_CODIGOINICIAL,
                         ori.ORI_CH_CONFEREAUTOMATICO,
                         ori.ORI_CH_CONFEREAUTOMATICOFAT,
                         EXU.SET_ST_CODIGO,
                         '      ' as UNIDADEEX
                    FROM lab_detalherequisicao der,
                         lab_examedependente   dep,
                         LAB_EXAME             EXA,
                         LAB_EXAMEUNIDADE      EXU,
                         LAB_METODO            MET,
                         LAB_MATERIAL          MAT,
                         LAB_EXAMEMETODO       EXM,
                         LAB_PRAZO             PRA,
                         LAB_PRAZO             PRA1,
                         LAB_ORIGEM            ORI,
                         LAB_EXAMETABELAPRECO  ETP1,
                         LAB_TABELA            TAB1,
                         LAB_REGRATABELAPRECO  RTP1,
                         LAB_REGRAS            REG1,
                         LAB_TABELAPRECO       TAP1
                   WHERE der.exa_st_codigo = dep.exa_st_codigo
                     AND der.uni_st_codigo = dep.uni_st_codigo
                     AND EXU.MET_ST_CODIGO = MET.MET_ST_CODIGO
                     AND EXA.MAT_ST_COLETA = MAT.MAT_ST_CODIGO
                     AND EXU.PRA_ST_CODIGO = PRA.PRA_ST_CODIGO(+)
                     AND EXU.EXU_ST_PRAZOURGENTE = PRA1.PRA_ST_CODIGO(+)
                     AND EXU.EXA_ST_CODIGO = EXM.EXA_ST_CODIGO
                     AND EXU.MET_ST_CODIGO = EXM.MET_ST_CODIGO
                     AND EXU.EXA_ST_CODIGO = EXA.EXA_ST_CODIGO
                     AND EXA.EXA_ST_CODIGO = dep.dep_st_codigo
                     AND ORI.UNI_ST_CODIGO = EXU.UNI_ST_CODIGO
                     AND REG1.uni_st_codigo = RTP1.uni_st_codigo
                     AND REG1.con_st_codigo = RTP1.con_st_codigo
                     AND REG1.reg_st_codigo = RTP1.reg_st_codigo
                     AND ETP1.tap_st_codigo = TAB1.tap_st_codigo
                     AND ETP1.tab_st_codigo = TAB1.tab_st_codigo
                     AND TAB1.TAB_CH_TIPO = '1'
                     AND TAB1.TAB_DT_INICIOVALIDADE =
                         (SELECT MAX(TAB_DT_INICIOVALIDADE) FROM LAB_TABELA
                           WHERE tap_st_codigo = TAB1.tap_st_codigo
                                 AND tab_st_codigo = TAB1.tab_st_codigo
                                 AND TAB_CH_TIPO = '1'
                                 AND TAB_DT_INICIOVALIDADE <= TO_DATE('2010-01-05','YYYY-MM-DD') )
                     AND TAB1.tap_st_codigo = TAP1.tap_st_codigo
                     AND RTP1.uni_st_codigo = EXU.UNI_ST_CODIGO
                     AND ETP1.exa_st_codigo = EXA.EXA_ST_CODIGO
                     AND RTP1.RTP_DT_INICIOVALIDADE =
                         (SELECT MAX(RTP_DT_INICIOVALIDADE)
                            FROM LAB_REGRATABELAPRECO
                           WHERE uni_st_codigo = RTP1.uni_st_codigo
                             AND con_st_codigo = RTP1.con_st_codigo
                             AND reg_st_codigo = RTP1.reg_st_codigo
                             AND RTP_DT_INICIOVALIDADE <= SYSDATE)
                     AND RTP1.RTP_IN_SEQUENCIA =
                         (SELECT MIN(RTP2.RTP_IN_SEQUENCIA)
                            FROM LAB_EXAMETABELAPRECO ETP2,
                                 LAB_TABELA           TAB2,
                                 LAB_REGRATABELAPRECO RTP2,
                                 LAB_TABELAPRECO      TAP2
                           WHERE ETP2.tap_st_codigo = TAB2.tap_st_codigo
                             AND ETP2.tab_st_codigo = TAB2.tab_st_codigo
                             AND TAB2.TAB_CH_TIPO = '1'
                             AND RTP2.uni_st_codigo = RTP2.uni_st_codigo
                             AND RTP2.con_st_codigo = RTP2.con_st_codigo
                             AND RTP2.reg_st_codigo = RTP2.reg_st_codigo
                             AND ETP2.tap_st_codigo = RTP2.tap_st_codigo
                             AND TAB2.tap_st_codigo = TAP2.tap_st_codigo
                             AND RTP2.uni_st_codigo = RTP1.UNI_ST_CODIGO
                             AND RTP2.con_st_codigo = RTP1.CON_ST_CODIGO
                             AND RTP2.reg_st_codigo = RTP1.REG_ST_CODIGO
                             AND ETP2.TAP_ST_CODIGO = ETP2.TAP_ST_CODIGO
                             AND ETP2.exa_st_codigo = ETP1.EXA_ST_CODIGO
                             AND RTP2.RTP_DT_INICIOVALIDADE =
                                 (SELECT MAX(RTP_DT_INICIOVALIDADE)
                                    FROM LAB_REGRATABELAPRECO
                                   WHERE uni_st_codigo = RTP1.uni_st_codigo
                                     AND con_st_codigo = RTP1.con_st_codigo
                                     AND reg_st_codigo = RTP1.reg_st_codigo
                                     AND tap_st_codigo = ETP1.tap_st_codigo
                                     AND RTP_DT_INICIOVALIDADE = RTP1.Rtp_Dt_Iniciovalidade))
                     AND EXU.UNI_ST_CODIGO ='TTT'
--//                 '    AND REG1.UNI_ST_CODIGO ='TTT'1' +
                     AND RTP1.CON_ST_CODIGO ='1'
                     AND RTP1.REG_ST_CODIGO ='434'
                     AND ORI.ORI_ST_CODIGO ='000001'
                   and DER.REQ_ST_CODIGO ='1024625884'




--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



--  Detalhes do Usuario


SELECT UMO.USU_ST_CODIGO USUUMO,
       PUM.PUS_ST_CODIGO,
       PUM.PUS_IN_SEQUENCIA,
       PUM.PUS_IN_SEQUENCIAPAI,
       PUM.MOD_ST_CODIGO,
       PUM.PUS_CH_HABILITADO,
       MDL.MOD_ST_DESCRICAO,
       MDL.MOD_ST_FORMULARIO,
       MDL.MOD_CH_STATUS,
       MDL.MOD_ST_OPCAO1,
       MDL.MOD_ST_OPCAO2,
       MDL.MOD_ST_OPCAO3,
       MDL.MOD_ST_OPCAO4,
       MDL.MOD_ST_OPCAO5,
       MDL.MOD_ST_OPCAO6,
       MDL.MOD_ST_OPCAO7,
       MDL.MOD_ST_OPCAO8,
       MDL.MOD_ST_OPCAO9,
       MDL.MOD_ST_OPCAO10,
       UMO.UMO_CH_OPCAO1,
       UMO.UMO_CH_OPCAO2,
       UMO.UMO_CH_OPCAO3,
       UMO.UMO_CH_OPCAO4,
       UMO.UMO_CH_OPCAO5,
       UMO.UMO_CH_OPCAO6,
       UMO.UMO_CH_OPCAO7,
       UMO.UMO_CH_OPCAO8,
       UMO.UMO_CH_OPCAO9,
       UMO.UMO_CH_OPCAO10,
       UMO.UMO_CH_OPCAO11,
       UMO.UMO_CH_OPCAO12,
       UMO.UMO_CH_OPCAO13,
       UMO.UMO_CH_OPCAO14,
       UMO.UMO_CH_OPCAO15,
       UMO.UMO_CH_OPCAO16,
       UMO.UMO_CH_OPCAO17,
       UMO.UMO_CH_OPCAO18,
       UMO.UMO_CH_OPCAO19,
       UMO.UMO_CH_OPCAO20,
       UMO.UMO_CH_OPCAO21,
       UMO.UMO_CH_OPCAO22,
       UMO.UMO_CH_OPCAO23,
       UMO.UMO_CH_OPCAO24,
       UMO.UMO_CH_OPCAO25,
       USU.USU_CH_ATIVO,
       USU.USU_CH_ANATOMIA,
       USU.USU_IN_DIASRELATORIO,
       USU.USU_CH_VALOREXTERNO
  FROM LAB_PERFILUSUARIOMODULO PUM,
       LAB_MODULO              MDL,
       LAB_USUARIO             USU,
       LAB_USUARIOMODULO       UMO
 where PUM.MOD_ST_CODIGO = MDL.MOD_ST_CODIGO
   AND UMO.USU_ST_CODIGO = USU.USU_ST_CODIGO
   AND UMO.MOD_ST_CODIGO = MDL.MOD_ST_CODIGO
   AND PUM.PUS_ST_CODIGO = '9'
   AND USU.USU_ST_CODIGO = 'EROS'
 order by PUM.PUS_ST_CODIGO, PUM.PUS_IN_SEQUENCIA








select det.req_st_codigo,det.exa_st_codigo,det.seq_st_codigo,det.REQ_DT_CADASTRO,det.DER_DT_FATURA,req.req_dt_cadastro,req.req_dt_fatura
from lab_detalherequisicao det , lab_requisicao req
where req.req_st_codigo = det.req_st_codigo and (det.der_dt_fatura <> trunc(det.der_dt_fatura) or det.REQ_DT_CADASTRO <> trunc(det.REQ_DT_CADASTRO))
and det.der_dt_fatura >= to_date('01-01-2010', 'dd-mm-yyyy')
and det.uni_st_codigo = 'TTT'
and req.uni_st_codigo = 'TTT'


select req_dt_cadastro from
lab_detalherequisicao
where req_st_codigo = '1025682009'

select req_dt_cadastro from
lab_detalherequisicao
where req_st_codigo = '1025682009'
and exa_st_codigo = 'TTT'



--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

select gtr.gtr_ch_triagem,
                 t.rec_st_codigo,
                 t.gtr_st_codigo,
                 t.mtr_ch_etiqueta,
                 t.mtr_ch_triagem,
                 (select max(amo_st_codigo) from lab_detalherequisicao
                   where req_st_codigo = '1025800119' ) as amostra,
                 rc.rec_st_descricao,d.*
            from lab_mapatriagem t,lab_grupoTriagem gtr,
                 (SELECT case
                           when der.grp_st_codigo is null then
                            der.exa_st_codigo
                           when der.seq_st_codigo = ('01') then
                            der.exa_st_codigo
                           else
                            der.grp_st_codigo
                         end as grupo,
                         DER.SEQ_ST_CODIGO,
                         DER.REQ_ST_CODIGO,
                         DER.EXA_ST_CODIGO,
                         DER.AMO_ST_CODIGO,
                         DER.AMO_ST_CODIGO as AMOSTRA1,
                         DER.SET_ST_CODIGO,
                         DER.DER_DT_ENTRADAMAT,
                         DER.LEG_ST_CODIGO,
                         DER.DER_CH_URGENTE,
                         NVL(DER.DER_CH_ETIQUETA, ('N'))  AS DER_CH_ETIQUETA,
                         DER.DER_DT_DATAPREVISTAENTREGA,
                         DER.DER_ST_UNIDADEEX,
                         DER.REC_ST_CODIGO,
                         DER.GRP_ST_CODIGO,
                         EXUEX.APO_ST_CODIGO,
                         PAC.PAC_ST_NOME,
                         PAC.PAC_IN_CODIGO,
                         PAC.PAC_ST_SEXO,
                         PAC.PAC_DT_NASCIMENTO,
                         LEG.LEG_IN_FONTE,
                         LEG.LEG_IN_COR,
                         LEG.LEG_ST_SIGLA,
                         EXA.EXA_ST_FANTASIA,
                         REQ.REQ_ST_QUARTO,
                         REQ.UNI_ST_CODIGO,
                         REQ.ORI_ST_CODIGO,
                         REQ.REQ_CH_NOVO,
                         REQ.REQ_ST_CODIGOLIS,
                        DER.REQ_ST_CODIGOLIS AS REQ_ST_CODIGOLISDER,
                         UNI.UNI_ST_DESCRICAO,
                         ORI_ST_MARCAETIQUETA,
                         ORI.ORI_ST_DESCRICAO,
                         MAT.MAT_ST_DESCRICAO,
                         exu.rec_st_coleta,
                  CASE DER.DER_CH_URGENTE || EXU.APO_ST_CODIGO
                       WHEN 'S' THEN
                            EXU.EXU_ST_UNIDADEEXURGENTE
                       WHEN 'N' THEN
                            EXU.EXU_ST_UNIDADEEX
                       ELSE '+' || EXU.APO_ST_CODIGO
                       END AS EXU_ST_UNIDADEEX,
                  CASE DER.DER_CH_URGENTE
                       WHEN 'S' THEN
                            EXU.EXU_ST_UNIDADEEXURGENTE
                       ELSE
                            EXU.EXU_ST_UNIDADEEX
                       END AS EXU_ST_UNIDADEEX1
                    FROM LAB_DETALHEREQUISICAO DER,LAB_PACIENTE PAC,LAB_REQUISICAO REQ,LAB_LEGENDA LEG,
                        LAB_EXAME  EXA,LAB_UNIDADE UNI,LAB_ORIGEM ORI,LAB_MATERIAL MAT,LAB_EXAMEUNIDADE EXU,LAB_EXAMEUNIDADE EXUEX
                   WHERE DER.REQ_ST_CODIGO = REQ.REQ_ST_CODIGO
                     AND REQ.PAC_IN_CODIGO = PAC.PAC_IN_CODIGO
                     AND DER.LEG_ST_CODIGO = LEG.LEG_ST_CODIGO
                     AND DER.DER_ST_UNIDADEEX = EXUEX.UNI_ST_CODIGO
                     AND DER.EXA_ST_CODIGO = EXUEX.EXA_ST_CODIGO
                     AND DER.UNI_ST_CODIGO = UNI.UNI_ST_CODIGO
                     AND DER.EXA_ST_CODIGO = EXA.EXA_ST_CODIGO
                     AND DER.UNI_ST_CODIGO = EXU.UNI_ST_CODIGO
                    AND DER.EXA_ST_CODIGO = EXU.EXA_ST_CODIGO
                     AND REQ.UNI_ST_CODIGO = ORI.UNI_ST_CODIGO
                     AND REQ.ORI_ST_CODIGO = ORI.ORI_ST_CODIGO
                     AND EXA.MAT_ST_TRIAGEM = MAT.MAT_ST_CODIGO(+)
                     AND DER.DER_CH_TROUXEMAT = 'S'
                     AND DER.REQ_ST_CODIGO = '1025800119') d,
                 LAB_RECIPIENTE RC
             where d.grupo = t.exa_st_codigo
             and t.gtr_st_codigo = gtr.gtr_st_codigo(+)
             and t.rec_st_codigo = rec_st_coleta
             and t.uni_st_codigo = d.uni_st_codigo
             and ((t.rec_st_codigo = d.REC_ST_CODIGO) or (d.REC_ST_CODIGO is null))
             and t.rec_st_codigo = rc.rec_st_codigo
             and (d.exa_st_codigo <> d.grp_st_codigo or d.grp_st_codigo is null)
             and d.grp_st_codigo is null






select * from lab_detalherequisicao
where usu_st_codigo = 'EROS'
and der_dt_entradamat
>= to_date('16/11/2009','dd/mm/yyyy');






select * from
lab_resultados
where req_st_codigo = '0915151515'
and exa_st_codigo = 'CPLA'

select * from lab_detalherequisicao
where req_st_codigo = '0915151515'
and exa_st_codigo = 'AMI'

select * from lab_configingresso
where exa_st_codigo = 'BIL'
AND met_st_codigo = 'RXL'
and cin_st_codigo = 'BDI'

select * from
lab_unidademedida
where unm_st_codigo = '007'


select * from DETALHEREQUISICOES
where EXAME IS NOT NULL
and EXAME = 'HEM'
AND REQUISICAO = '1027838917'
AND POSTO = '001'
AND PRONTUARIO = '10595804'

select * from GRUPO

select * from EXAMESAGRUPADOS

select * from EXAMESDEPENDENTES
where exame = 'PSA'

select * from SETORES
where SETOR = '5'

select * from RECIPIENTES

select * from exames

SELECT * FROM EXAMES 
where EXAME = 'HEM'
WHERE EXAME = 'HIV3'

select * from EXAMESDEPENDENTES
where exame = 'HIV'

select descricao from
POSTOS WHERE POSTO = '002'


SELECT
SETOR
FROM
	EXAMES
WHERE
	EXAME = 'HIV'

SELECT * FROM
EXAMESDEPENDENTES
WHERE EXAME = 'HIV'

SELECT * FROM 
EXAMES WHERE EXAME = 'HIV1'


select * from POSTOS
where posto = '001'

SELECT * FROM 
DETALHEREQUISICOES
WHERE PRONTUARIO = '15582990'
AND POSTO = '001'


SELECT
	NEXT
	value 
	FOR 
	GEN_DETALHEREQUISICOES
	
	SHOW GENERATORS;
	SHOW SEQUENCES;


select * from 
REQUISICOES 
WHERE ENTRADA 
 <= '2010/05/01'
 
 select * from REQUISICOES
 where REQUISICAO = '0807826320'
 
 select * from 
 REQUISICOES 
 WHERE
 ENTRADA BETWEEN
  '2010/02/18' AND '2010/02/19'
  
  select * from
  DETALHEREQUISICOES
  WHERE PRONTUARIO = '11579437'
  
  select * from POSTOS
  select * from SETORES
  select * from UNIDADES
  select * from UNIDADEATENDIMENTO

select * from DETALHEREQUISICOES
WHERE posto = '001'
--and REQUISICAO = '1025121087'
and ENTRADA <= '2010/05/01'


SELECT min(ENTRADA)
FROM DETALHEREQUISICOES


select * 
from 
DETALHEREQUISICOES 
WHERE POSTO = '001'
(select max(REQUISICAO) from DETALHEREQUISICOES )


Set rowcount 1
Select * from DETALHEREQUISICOES order by AMOSTRA desc

select 
FIRST 1
from 
DETALHEREQUISICOES
order by AMOSTRA DESC


select * from RESULTADOS
where REQUISICAO = '1024873772'


select * from DETALHEREQUISICOES
where POSTO = '001'
and requisicao = '1024873771'

select * from RESULTADOS
where POSTO = '001'
and prontuario = '15707579'
and requisicao = '1024873771'
and amostra = '2010102487377101'

select * from REQUISICOES
where posto = '001'
and REQUISICAO = '1024873772'

select * from DETALHEREQUISICOES
where posto = '001'
and REQUISICAO = '1024873772'


select * from RESULTADOS

where posto = '001'
and prontuario = '15707618'
and requisicao = '1024873772'
and amostra = '2010102487377201'
and datatestcomplet is not null

renato shoel
2516
133043 8


 select * from RESULTADOS
 where AMOSTRA = '2008080920854001';
 
 select * from RESULTADOS
 where REQUISICAO = '0809208540'
 


select * from DETALHEREQUISICOES
where posto = '001'
and  setor = '004'
and AMOSTRA like '2010%'

select 	GENERATOR_ID
FROM RDB$GENERATORS


select * from
LAB_AMOSTRA
where AMO_DT_DATAENTRADA = '2010/05/01'


select * from
lab_amostra
where REQ_ST_REQUISICAO = '1027572680'




select * from
lab_amostra
where pac_st_prontuario = '14664422'

select * from 
resultados
where
posto = '001' 
and 
prontuario = '14664422'
and 
amostra = '2010102757268001'



select * from
Lab_Amostra_Setor
where AMO_ST_AMOSTRA = '2010102757268001'

select * from
Lab_AmostraPerfil
where AMO_ST_AMOSTRA = '2010102757268001'

select * from 
lab_amostra
where 
amo_st_amostra = '2010091832481006'
--pac_st_prontuario = '15831444'

select * from
resultados
where AMOSTRA
= '2010102757268001'


select * from
lab_amostra
where PAC_ST_PRONTUARIO
= '15886078'


select * from REQUISICOES
where PRONTUARIO
= '15886078'


select * from
exames 
where EXAME
= 'GLI'

select * 
from Setores




select * from LAB_AMOSTRA AS LA
JOIN LAB_AMOSTRA_SETOR AS LS
ON LA.AMO_ST_AMOSTRA
=
LS.AMO_ST_AMOSTRA
AND
LA.POS_ST_POSTO = '001'
AND 
LS.SET_ST_SETOR = LA.POS_ST_POSTO



SELECT  LA.POS_ST_POSTO,LA.AMO_ST_AMOSTRA,LA.PAC_ST_PRONTUARIO
FROM 
LAB_AMOSTRA as LA,LAB_AMOSTRA_SETOR as LS
WHERE LA.POS_ST_POSTO = '001'
and
LA.AMO_ST_AMOSTRA = LS.AMO_ST_AMOSTRA




SELECT NEXT VALUE FOR GEN_REQUISICAO FROM RDB$DATABASE


SELECT NEXT VALUE FOR GEN_USUARIOS FROM RDB$DATABASE

SELECT NEXT VALUE FOR GEN_PRONTUARIO FROM RDB$DATABASE



select * from
USUARIOSSETORES
where USUARIO = '424'
and SETOR = '001'

<!-- <property name="hibernate.connection.url">jdbc:firebirdsql://192.168.1.194:3050/c:/tminterface/dados/tminterface.tm?encoding=WIN1252</property> --.>


INSERT INTO RECIPIENTES (RECIPIENTE, DESCRICAO)
 VALUES (
'00001',
'TUBO ROXO'
)

SELECT *
FROM RECIPIENTES;


select * from REQUISICOES
where requisicao = '1000000159'

select * from Pacientes
where prontuario = '10061000000026'



select * from USUARIOSSETORES

INSERT INTO USUARIOSSETORES (USUARIO, SETOR)
 VALUES (
'423',
'005'
)

select * from USUARIOSSETORES
where USUARIO = '424'

delete FROM USUARIOSSETORES
where USUARIO = '424'
and    SETOR = '3'

INSERT INTO USUARIOSSETORES (USUARIO, SETOR)
 VALUES (
'424',
'005'
)

UPDATE USUARIOS
--SET RELINTERFACEAMENTO = 'S'
--SET RELEXAME = 'S'
SET INCLUIREXAME = 'S'
WHERE USUARIO = '424'

SELECT * FROM USUARIOS WHERE USUARIO = '423'
UNION ALL
SELECT * FROM USUARIOS WHERE USUARIO = '424'

select * from 
pacientes 
where
entrada = '2010-06-11'


select * from 
pacientes where 
NOME like 'EROS KOLLER%'

select * from
pacientes where
entrada = '2010-06-15'

select * from
REQUISICOES where
entrada = '2010-06-15'


select * from 
requisicoes 
where requisicao = '1000000180'

select * from 
lab_amostra
where req_st_requisicao = '1028648089'

select u.cadastros,u.cadastrosuporte from Usuarios as u
where u.usuario = '422'
union all
select u.cadastros,u.cadastrosuporte from Usuarios as u
where u.usuario = '424'

update USUARIOS
--set cadastros = 'S'
set cadastrosuporte = 'S'
where USUARIO = '424'


select
        this_.AMO_ST_AMOSTRA as AMO1_33_1_,
        this_.AMO_DT_ARMAZENAMENTO as AMO2_33_1_,
        this_.AMO_DT_ASSINATURA as AMO3_33_1_,
        this_.AMO_DT_DATAENTRADA as AMO4_33_1_,
        this_.AMO_DT_LIBERACAO as AMO5_33_1_,
        this_.AMO_DT_RECEBIMENTO as AMO6_33_1_,
        this_.AMO_HR_ARMAZENAMENTO as AMO7_33_1_,
        this_.AMO_HR_ASSINATURA as AMO8_33_1_,
        this_.AMO_HR_HORAENTRADA as AMO9_33_1_,
        this_.AMO_HR_LIBERACAO as AMO10_33_1_,
        this_.AMO_HR_RECEBIMENTO as AMO11_33_1_,
        this_.AMO_IN_FLAG as AMO12_33_1_,
        this_.AMO_IN_STATUS as AMO13_33_1_,
        this_.AMO_ST_AMOSTRAORIGINAL as AMO14_33_1_,
        this_.AMO_ST_DIAGNOSTICO as AMO15_33_1_,
        this_.AMO_ST_ENVIADOLIS as AMO16_33_1_,
        this_.AMO_ST_ENVIOLIS as AMO17_33_1_,
        this_.AMO_ST_IDAMOSTRAHC as AMO18_33_1_,
        this_.AMO_ST_IDPEDIDOHC as AMO19_33_1_,
        this_.AMO_ST_INDICACAOCLINICA as AMO20_33_1_,
        this_.AMO_ST_INDICACAOOUTROS as AMO21_33_1_,
        this_.AMO_ST_MATERIAL as AMO22_33_1_,
        this_.AMO_ST_MEDICAMENTO as AMO23_33_1_,
        this_.AMO_ST_MOTIVONOVACOL as AMO24_33_1_,
        this_.AMO_ST_MOTIVONOVACOLETA as AMO25_33_1_,
        this_.AMO_ST_OBSERVACOES as AMO26_33_1_,
        this_.AMO_ST_POSICAOESTANTE as AMO27_33_1_,
        this_.AMO_ST_SETORORIGEM as AMO28_33_1_,
        this_.AMO_ST_TIPOAMOSTRA as AMO29_33_1_,
        this_.AMO_ST_VOLUME as AMO30_33_1_,
        this_.AMO_ST_CADASTROIMAGE as AMO31_33_1_,
        this_.CAF_ST_CODIGO as CAF32_33_1_,
        this_.EST_ST_CODIGO as EST33_33_1_,
        this_.INS_ST_CODIGO as INS34_33_1_,
        this_.N1AMO_ST_AMOSTRA as N35_33_1_,
        this_.NAMO_ST_AMOSTRA as NAMO36_33_1_,
        this_.OBS_ST_CODIGO as OBS37_33_1_,
        this_.ORI_ST_CODIGO as ORI38_33_1_,
        this_.ORI_ST_ORIGEM as ORI39_33_1_,
        this_.PAC_ST_PRONTUARIO as PAC47_33_1_,
        this_.POS_ST_POSTO as POS40_33_1_,
        this_.PRI_ST_PRIORIDADE as PRI41_33_1_,
        this_.REQ_ST_REQUISICAO as REQ42_33_1_,
        this_.USU_ST_ASSINATURALAU as USU43_33_1_,
        this_.USU_ST_ASSINATURALAUDO as USU44_33_1_,
        this_.USU_ST_LIBERACAOLAUDO as USU45_33_1_,
        this_.USU_ST_USUARIO as USU46_33_1_,
        pacientes2_.PRONTUARIO as PRONTUARIO43_0_,
        pacientes2_.ALTERADO as ALTERADO43_0_,
        pacientes2_.BAIRRO as BAIRRO43_0_,
        pacientes2_.CELULAR as CELULAR43_0_,
        pacientes2_.CEP as CEP43_0_,
        pacientes2_.CIDADE as CIDADE43_0_,
        pacientes2_.COMPLEMENTO as COMPLEME7_43_0_,
        pacientes2_.CONVENIO as CONVENIO43_0_,
        pacientes2_.COR as COR43_0_,
        pacientes2_.CPF as CPF43_0_,
        pacientes2_.DESTINO as DESTINO43_0_,
        pacientes2_.DUM as DUM43_0_,
        pacientes2_.EMAIL as EMAIL43_0_,
        pacientes2_.ENDERECO as ENDERECO43_0_,
        pacientes2_.ENTRADA as ENTRADA43_0_,
        pacientes2_.ENTRADAHORA as ENTRADA16_43_0_,
        pacientes2_.ESTADOCIVIL as ESTADOC17_43_0_,
        pacientes2_.FOTO as FOTO43_0_,
        pacientes2_.IDADE as IDADE43_0_,
        pacientes2_.MAE as MAE43_0_,
        pacientes2_.MATRICULA as MATRICULA43_0_,
        pacientes2_.MEDICO as MEDICO43_0_,
        pacientes2_.NASCIMENTO as NASCIMENTO43_0_,
        pacientes2_.NOME as NOME43_0_,
        pacientes2_.NUM_SAME as NUM25_43_0_,
        pacientes2_.NUMERO as NUMERO43_0_,
        pacientes2_.OBSERVACOES as OBSERVA27_43_0_,
        pacientes2_.ORIGEM as ORIGEM43_0_,
        pacientes2_.PAC_ST_SUS as PAC29_43_0_,
        pacientes2_.POSTO as POSTO43_0_,
        pacientes2_.PROFISSAO as PROFISSAO43_0_,
        pacientes2_.PRONTUARIOUNIDADE as PRONTUA32_43_0_,
        pacientes2_.REGRA as REGRA43_0_,
        pacientes2_.RG as RG43_0_,
        pacientes2_.SEXO as SEXO43_0_,
        pacientes2_.TELEFONE as TELEFONE43_0_,
        pacientes2_.UF as UF43_0_,
        pacientes2_.VALIDADECARTEIRA as VALIDAD38_43_0_ 
    from
        LAB_AMOSTRA this_ 
    left outer join
        PACIENTES pacientes2_ 
            on this_.PAC_ST_PRONTUARIO=pacientes2_.PRONTUARIO 
    where
        this_.REQ_ST_REQUISICAO='1028648089'
        
        
        
        select
       *
    from
        LAB_AMOSTRA this_ 
    left outer join
        PACIENTES pacientes2_ 
            on PAC_ST_PRONTUARIO=pacientes2_.PRONTUARIO 
    where
        this_.PAC_ST_PRONTUARIO='15927743'
        
        select * from 
        configingresso 
        where exame = 'TSH'
        
        
        
        select * from
        equipamentos
        where 
        equipamento = '0044'
        
        select * from 
        Pacientes
        where 
        ENTRADA BETWEEN '2009-04-06' AND  '2010-06-30'
        and 
        pac_st_sus is not null
        
        select * from
        usuarios
        where usuario = '422'
        union all
        
        select * from
        usuarios
        where usuario = '424'
        
        update USUARIOS
        set INCLUIREXAME = 'S',
         CANCELAEXAME = 'S',
         REPETEEXAMES = 'S',
         ARQUIVOMORTO = 'S',
         REQUISICOES = 'S',
         CADASTROSUPORTE = 'S',
         menudiario =  'S',
         CADASTROS = 'S',
         RELATORIOS = 'S',
         FERRAMENTAS = 'S',
         DELETAEXAME = 'S',
         CANCELAAMOSTRA = 'S',
         RECIPIENTESCOLETA = 'S', 
         CADASTROMEDICOS = 'S',
         ESPECIALIDADEMEDICA = 'S',
         SOROTECARACK = 'S',
         SOROTECAFREEZER = 'S',
         RELEXAMESALTERADOS = 'S',
         RELEXAMESESTA = 'S',
         RELCODEXAMES = 'S',
         RELPOSTOS = 'S',
         RELOBSERVACOES = 'S',
         RELCODBARRAS = 'S',
         INTERFAUNIDADES = 'S',
         INTERFAEQUIPAMENTOS = 'S',
         INTERFASETORES = 'S',
         CADASTROSPERFIL = 'S',
         CADASTROSOBS = 'S',
         CADASTROSPOSTOS = 'S',
         CADASTROSMOTIVOS = 'S',
         CADASTROSMATERIAIS = 'S',
         CADASTROSMICRO = 'S',
         CADASTRODEPACIENTES = 'S',
         FORMATACAODEEXAMES = 'S',
         ALTERAPRONTUARIO = 'S',
         VERIFICAAMOSTRA = 'S',
         GERENCIAMENTOREL = 'S',
         ATUALIZAPARAMETROS = 'S',
         NAONECESSARIO = 'S',
         INGRESSORESULTADOS = 'S',
         IMPRESSAOPARCIAL = 'S',
         LIBERABLOQUEADOLOTE = 'S'
        where usuario = '424'
        up

select * from USUARIOS
where USUARIO = '423' 


select * from EXAMES
where EXAME= 'GLI'
UNION ALL
select * from EXAMES
where EXAME= 'TSA'

select * from
RECIPIENTES
where RECIPIENTE = '00001'

update EXAMES
set RECIPIENTE = '00001',
RECIPIENTETRIAGEM = '00001'
WHERE EXAME = 'HB'

select * from
DETALHEREQUISICOES
where PRONTUARIO = '10063000000057'


DROP TABLE LAB_BARCODES;

create
 table
  LAB_BARCODES 
(
	BAR_IN_CODE INTEGER  not null PRIMARY KEY,
	BAR_DT_DATABUIDED DATE not null ,
	BAR_DT_HORABUIDED TIME not null ,
	BAR_DT_DATAACCESSED DATE ,
	BAR_DT_HORAACCESSED TIME ,
	BAR_ST_IP VARCHAR(15) not null ,
	BAR_ST_OS VARCHAR(55) ,
	BAR_ST_ZEBRA BLOB ,
	BAR_ST_ARGOX BLOB ,
	BAR_CH_DOWNLOADED CHAR(1) DEFAULT 'N'
);


Column Name            Type       Size    Primary Key    Foreign Key    Nullable    Scale
BAR_IN_CODE            INTEGER    10      true                          NO               
BAR_DT_DATABUIDED      DATE       10                                    NO               
BAR_DT_HORABUIDED      TIME       8                                     NO               
BAR_DT_DATAACCESSED    DATE       10                                    YES              
BAR_DT_HORAACCESSED    TIME       8                                     YES              
BAR_ST_IP              VARCHAR    255                                   NO               
BAR_ST_OS              VARCHAR    255                                   YES              
BAR_ST_ZEBRA           VARCHAR    255                                   YES              
BAR_ST_ARGOX           VARCHAR    255                                   YES              
BAR_CH_DOWNLOADED      CHAR       1                                     YES              

9 999 999 999

CREATE GENERATOR gen_labbarcode_id;
SET GENERATOR gen_labbarcode_id TO 0;


set term !! ;
CREATE TRIGGER labbarcode_BI FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_labbarcode_ID, 1);
END!!
set term ; !!


SELECT NEXT VALUE FOR GEN_LABBARCODE_ID FROM RDB$DATABASE

delete from LAB_BARCODES
where bar_in_code = 1 




select * from 
lab_barcodes

select * from
DetalheRequisicoes



where PRONTUARIO = '10070100000062'

select * from 
Lab_barcodes
where BAR_ST_IP = '187.71.124.7'








CREATE TABLE `MV_LOTE_SOLICITACAO_EXAMES` (
  `MV_IN_CODIGO` bigint(20) NOT NULL auto_increment,
  `MV_CH_STATUS` char(1) default NULL,
  `MV_ST_UNIDADE` varchar(20),
  `MV_DT_RECEBIMENTO` date default NULL,
  `MV_BL_ARQUIVO` text,
  PRIMARY KEY  (`MV_IN_CODIGO`),
  KEY `CH_INDEX` (`MV_CH_STATUS`)
) ENGINE=MyISAM AUTO_INCREMENT=25323 DEFAULT CHARSET=latin1  


ovridapa

select * 
from 
Usuarios
where 
senha = 'ovridapa'




select * from
DETALHEREQUISICOES
where 
 requisicao = '1028592643'
 
 
 select * from
 Resultados
 where AMOSTRA = '2010102859264301'
 
 select * from 
 lab_amostra
 where AMO_ST_AMOSTRA = '2010102859264301'
 
 select * from
 detalherequisicoes
 where
  amostra like '100721038594'
  
  
  
  select * from
  lab_amostra
  where amo_st_amostra like '1007%'
  
  
  
 
 
 select * from 
 Requisicoes
  where     
   requisicao = '1007210385'
   
   
   
   select * from
    DETALHEREQUISICOES
	where AMOSTRA = '2010100000025000'
	
	select * from
	Detalherequisicoes
	where posto = '001'
	and prontuario = '10203040'
	and requisicao = '1000000252'
   
   
   100722040561
   
   select * from
   lab_amostra_setor
   where AMO_ST_AMOSTRA = '100722040561'
   
   select * from
   lab_amostraperfil
   where AMO_ST_AMOSTRA = '100722040561'
   
   
   
   
   select * from
   lab_amostra_setor
   where AMO_ST_AMOSTRA = '2010102769371002'
    select * from
   lab_amostraperfil
   where AMO_ST_AMOSTRA = '2010102769371002'
  
   
   select req_st_codigo, leg_st_codigo from lab_detalherequisicao where uni_st_codigo = 'TTT' and der_dt_fatura >= to_date('20-07-2010', 'dd-mm-yyyy') and
leg_st_codigo in ('001', '002', '003')
-- (leg_st_codigo = '001' or leg_st_codigo = '002' or leg_st_codigo = '003')

select * from
lab_amostra
where 
(AMO_IN_STATUS = '001' or AMO_IN_STATUS = '002' or AMO_IN_STATUS = '003')


select * from
Exames 
where ATIVO = 'S'

select * from DETALHEREQUISICOES
where AMOSTRA = '2010102769341223';

select * from RESULTADOS
where AMOSTRA = '2010102769341223';


select * from EXAMESDEPENDENTES
where EXAME = 'HIV'

select * from 
examesagrupados

select * from DETALHEREQUISICOES
where REQUISICAO = '1027819243'
and exame = 'POT'
AND USUARIOASSINATURA
= 'EROS'
--ORDER BY EXAME
UNION ALL
select * from DETALHEREQUISICOES
where REQUISICAO = '1027819243'
and exame = 'BIL'
AND USUARIOASSINATURA
= 'EROS'
--ORDER BY EXAME ASC


select * from DETALHEREQUISICOES
where REQUISICAO = '1027819243'
and exame = 'BIL'
--and sequencia = '004'
ORDER BY SEQUENCIA DESC



select * from RESULTADOS
where 
POSTO = '001'
AND PRONTUARIO = '15449674'
AND REQUISICAO = '1027819243'
AND AMOSTRA = '2010102781924321'






   
select count(*) as qtd_rowns from lab_barcodes
where bar_in_code is not null;


select * from 
Requisicoes
where REQUISICAO = '1027819243'

select status from
detalherequisicoes
where entrada <= '2010-06-05' --TO_DATE('2010-06-05','YYYY-MM-DD')


select * from REQUISICOES
where REQUISICAO = '1007260446'   
 


CREATE TABLE `ENVIA_LOTE_SOLICITACAO_EXAMES` (
  `ENVIA_IN_CODIGO` bigint(20) NOT NULL auto_increment,
  `ENVIA_CH_STATUS` char(1) default NULL,
  `ENVIA_DT_RECEBIMENTO` date default NULL,
  `ENVIA_BL_ARQUIVO` text,
  PRIMARY KEY  (`ENVIA_IN_CODIGO`),
  KEY `CH_INDEX` (`ENVIA_CH_STATUS`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1



CREATE TABLE `CIE_LOTE_SOLICITACAO_EXAMES` (
  `CIE_IN_CODIGO` bigint(20) NOT NULL auto_increment,
  `CIE_CH_STATUS` char(1) default NULL,
  `CIE_ST_UNIDADE` varchar(20) default NULL,
  `CIE_DT_RECEBIMENTO` date default NULL,
  `CIE_BL_ARQUIVO` text,
  PRIMARY KEY  USING BTREE (`CIE_IN_CODIGO`),
  KEY `CH_INDEX` (`CIE_CH_STATUS`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1



select uf from
cep_log_index
where cep5 = '03543';



select * from
sp
where cep = '03543-080';

select * from
uf
where Cep2 = '03543'

delete from
ENVIA_LOTE_SOLICITACAO_EXAMES
where ENVIA_IN_CODIGO = 7;


select * from 
usuarios
where NOME = 'EROS'

select recipiente from
Exames
where 
exame = 'HEM'
UNION ALL

select recipiente from
Exames
where 
exame = 'GLI'


update exames 
set recipiente = '00001'
where exame = 'TSA'


update USUARIOS

set usuarios = 'S',
relinterfaceamento = 'S',
arquivomorto = 'S',
requisicoes = 'S',
cadastrosuporte = 'S',
menudiario = 'S',
cadastros = 'S',
relatorios = 'S',
ferramentas = 'S',
deletaexame = 'S',
cancelaamostra = 'S',
recipientescoleta = 'S',
cadastromedicos = 'S',
especialidademedica = 'S',

sorotecarack = 'S',
sorotecafreezer = 'S',
relexamesalterados = 'S',
relexameesta = 'S',
relcodexames = 'S',
relpostos = 'S',
relobservacoes = 'S',
relcodbarras = 'S',
interfaunidades = 'S'

where USUARIO = '431'


select * from usuarios
where USUARIO = '424'
union all
select * from usuarios
where USUARIO = '431'


delete from USUARIOS
where usuario = '425'

insert into USUARIOSSETORES
values('431','001');


select * from exames
where EXAME = 'TRI'
union all
select * from exames
where EXAME = 'ABO'


select * from
setores 
where SETOR = '005'

select * from
materiais
where MATERIAL = '016'

select * from 
recipientes
where RECIPIENTE = '00001'

select * from exames
where RECIPIENTE = '00002'


update Exames
set recipiente = '00001'
where recipiente = '00002';
	
	
	select * from
	Exames 
	where EXAME like 'pc%'
	
	
	delete from LAB_BARCODES
	where BAR_IN_CODE > 0

 
 select * from
 lab_amostra
 
 select * from 
 lab_barcodes
 order by BAR_IN_CODE asc
 
 select * from
 lab_barcodes
 where BAR_ST_REQUISICAO = '1009220931'
 
 select * from EXAMES
 where EXAME = 'TSA'
 
 UPDATE EXAMES
 SET MATERIAL = '001'
 WHERE EXAME = 'CLURE'
 
 select * from EXAMES
 where EXAME = 'PSA'
 
 
 select * from
 exames
 where 
  material = ''
  
  
  select * from
  lab_barcodes
  order by bar_in_code desc
  
  delete from LAB_BARCODES
  where BAR_IN_CODE > 1962
  
  select * from
  DETALHEREQUISICOES
  where REQUISICAO = '100924100276'
  
  
 select * from
 EXAMES
 where EXAME = 'TRI'


insert into
 SETORES
  values('999','SEM SETOR',0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
  
  select * from Setores
  where setor = '999'
  
  
  update POSTOS
  set RODAPE = '<style>.umavezp1{background-repeat:no-repeat;background-position:100%;}</style>
<table cellspacing="0" cellpadding="0" width="100%" border="0" align="center">
<script>

</script>
    <td class="umavezp1" width="170" align="center" height="56" background="<#CAMINHO><#ASSINA1>">&nbsp;</td>
  <tr>
    <td align="center"><span class="fonte1p"><#FRASE><br></span></td>
  </tr>
  <tr>
    <td align="center" class="linhasp"><span class="fonte2p">&#8220;Os resultados obtidos devem ser correlacionados com outros dados cl&iacute;nicos.&#8221;<br>Laborat&oacute;rio participante do programa PELM da Sociedade Brasileira de Patologia Cl&iacute;nica.<br>Laboratório registrado no CRM/SP sob o nº 42679 - Responsável Técnico: Dr. Luiz Gastão Mange Rosenfeld CRM/SP 13707<br>Licença de Funcionamento na Vigilância Sanitária nº 350570801-864-000003-1-0 e 000002-1-3<br><#FRASERODAPE></span></td>
  </tr>
  <tr>
    <td align="right"><span class="fonte2p"><b>Data da Impressão:</b>&nbsp;<#DATA> <#HORA> <#PAGINA></span></td>
  </tr>
</table>',
  CABEC = '<table cellSpacing=0 cellPadding=0 width="100%" border=0>
 <tr> 
   <td width="20%" height=84 align="center"><table cellSpacing=0 cellPadding=0 width="97" height="84" border="0" align="center"><tr><td class="umavezc" background="<#CAMINHO>LOGOGRU.DAT"></td></tr></table></td>
   <td width="60%" align="center" valign="middle"><span class="fonte4ci">Laboratório de Analises Clinicas<br>Prefeitura de Guarulhos</span><br><span class="fonte3c"><b>Av: Dr. Renato de Andrade Maia, 1337<br>Telefone:(11) 6409-0536</b></span></td>
   <td width="20%" align="center"><table cellSpacing=0 cellPadding=0 width="143" height="59" border="0" align="center"><tr><td class="umavezc" background=""></td></tr></table></td>
 </tr>
</table>'
WHERE POSTO = '001'




SELECT NEXT VALUE FOR GEQUIPAMENTO FROM RDB$DATABASE

select * from
Exames
where EXAME = 'AAU'

select * from
Examesequipamentos
where EQUIPAMENTO = '0071'


select * from
EXAMESMETODOS
WHERE EXAME

select * from
EXAMESEQUIPAMENTOS
where EXAME = 'CLEU'

select * from
EQUIPAMENTOS
WHERE EQUIPAMENTO =  '0148'

DELETE FROM EXAMESEQUIPAMENTOS
WHERE EQUIPAMENTO = '0131'


select * from
EXAMESEQUIPAMENTOS
where EXAME = 'TRI'


select * from
LAB_AMOSTRA
WHERE REQ_ST_REQUISICAO = '1027485886'


select * from
LAB_AMOSTRA_SETOR
WHERE 
AMO_ST_AMOSTRA = '2010102748588602'
 




EXECUTE 
PROCEDURE 
STATUSAMOSTRAPERFIL_NEW
2010102748588602,001,null;



execute
procedure 
STATUSAMOSTRAPERFIL_NEW
2010102748588602,001,null;



select * from
LAB_AMOSTRA
where AMO_ST_AMOSTRA = '101026118619'


select * from
LAB_AMOSTRA_SETOR
where AMO_ST_AMOSTRA = '101026118803'

SELECT * FROM
EXAMES 
WHERE EXAME = 'HB'


insert into EXAMESMETODOS
VALUES('GLI','EROS','ZERO',NULL,'S');



select * from
RESULTADOS
where 
REQUISICAO = '1027758208'
AND EXAME = 'HEM'


select * from
REQUISICOES
WHERE OBSERVACAO IS NOT NULL
AND REQUISICAO LIKE '%1027%'

select * from
Resultados 
where REQUISICAO = '1027372262'
and EXAME = 'PCR'

select * from
Resultados 
where REQUISICAO = '1027372262'
and EXAME = 'HEM'


select * from
Resultados 
where REQUISICAO = '1027928556'
and EXAME = 'HEM'


SELECT * FROM
RESULTADOS
WHERE AMOSTRA = '2010102792855601'

SELECT * FROM
EXAMES
WHERE EXAME = 'GLI'


select * from
Resultados 
where REQUISICAO = '1027758179'
and EXAME = 'BIL'

select * from
CONFIGINGRESSO
WHERE EXAME = 'BIL'
AND EQUIPAMENTO = '0088'

SELECT * FROM
LAB_AMOSTRA 
WHERE REQ_ST_REQUISICAO = '1027758179'
AND AMO_ST_AMOSTRA = '2010102775817907'


SELECT * FROM
REQUISICOES
WHERE REQUISICAO = '1027758179' 



select * from
Resultados 
where REQUISICAO = '1027758179'
and EXAME = 'TP'



4191-5530 LIVIA SIMCORP
4191-2608 LIVIA SIMCORP



SELECT * FROM
DETALHEREQUISICOES
WHERE REQUISICAO =  '1027758179'
AND EXAME = 'BIL'
AND  AMOSTRA = '2010102775817907'


SELECT * FROM
RESULTADOS
WHERE REQUISICAO =  '1027758179'
AND EXAME = 'BIL'
AND  AMOSTRA = '2010102775817907'
AND PRONTUARIO = '15498783'
AND SEQUENCIA = '006'










select * from
Resultados 
where REQUISICAO = '1027133831'
and EXAME = 'CKMB'



select * from 
DETALHEREQUISICOES
WHERE REQUISICAO = '1027693469'
AND AMOSTRA = '2010102769346921'


select * from
DETALHEREQUISICOES
WHERE AMOSTRA = '2010102789422921'

select * from
DETALHEREQUISICOES
WHERE REQUISICAO = '1027894229'
and REPETICAO IS NOT NULL


select * from
LAB_AMOSTRA
WHERE AMO_ST_AMOSTRA = '2010102789422921'



FLAGCKMB                                          
  CKMB
  
  
  
  select * from
  REQUISICOES where REQUISICAO = '1012071241'
  union all
  
  select * from
  REQUISICOES where REQUISICAO = '1027758271'
  
  select * from RESULTADOS
  WHERE REQUISICAO = '1012081243'
  UNION ALL

  select * from RESULTADOS
  WHERE REQUISICAO = '1027758271'
  
  
INSERT 
INTO 
WSCIE_USER (USER_ST_NOME,USER_ST_SENHA,USER_ST_UNIDADE,USER_ST_UNIDADEDESC,USER_DT_DATA)
VALUES
(
'TEST',
'TEST',
'TEST',
'TEST',
'2010/02/19');

select * from
	WSCIE_LOTE_SOLICITACAO_EXAMES
	WHERE WSCIE_IN_CODIGO IS NOT NULL
	ORDER BY WSCIE_IN_CODIGO DESC
	
	
	select * from 	ENVIA_LOTE_SOLICITACAO_EXAMES
		WHERE ENVIA_IN_CODIGO IS NOT NULL
		ORDER BY ENVIA_IN_CODIGO DESC
		

insert into USUARIOSSETORES
values('431','001');

select * from USUARIOS where
USUARIO = '431'
UNION ALL 
select * from USUARIOS where
USUARIO = '422'

update USUARIOS 
SET CADASTROSRECIPIENTES = 'S'
WHERE USUARIO = '424'



update USUARIOS 
SET FORMATACAOEXAMES = 'S'
WHERE USUARIO = '424'


		select * from RESULTADOS
		WHERE REQUISICAO = '1028270198'
		AND PRONTUARIO = '15754526'
		AND AMOSTRA = '2010102827019806'  
		and posto = '001'
		order by SEQRESULTADO ASC
		
		
		select * from RESULTADOS
		WHERE REQUISICAO = '1028315085'
		AND PRONTUARIO = '15754722'
		AND AMOSTRA = '2010102831508502'  
		and posto = '001'
		order by SEQRESULTADO ASC
		
		
		
			select * from RESULTADOS
		WHERE REQUISICAO = '1028270193'
		AND PRONTUARIO = '15754877'
		and posto = '001'
		AND AMOSTRA = '2010102827019302'  
		
		select * from Exames
		where EXAME = 'HEM'
		
		select AMO_ST_SETORORIGEM from LAB_AMOSTRA
		WHERE AMO_ST_AMOSTRA = '2010102827019302'
		
		

		order by SEQRESULTADO ASC
		
		
		
		
		
		SELECT * FROM EXAMESMETODOSVERSAO 
		WHERE EXAME = 'HEM'
		
		SELECT * FROM CONFIGINGRESSO
		WHERE EXAME = 'HEM'
		AND EQUIPAMENTO = '0000'
		
		
		



create
 table
  USUARIOSPOSTOS 
(
	USU_ST_CODIGO VARCHAR(4)  not null ,
	POSTO_ST_CODIGO VARCHAR(4)  not null,
	PRIMARY KEY (USU_ST_CODIGO,POSTO_ST_CODIGO)
)

--insert into USUARIOSPOSTOS VALUES ('424','001');
insert into USUARIOSPOSTOS VALUES ('431','002');





select * from
USUARIOSPOSTOS 
where USU_ST_CODIGO = '423'


select FORMATACAODEEXAMES,FORMATACAOEXAMES,USUARIOS from
USUARIOS
where USUARIO = '422'
		
		
